Update multiple rows with multiple values and multiple conditions mysql

Question

I am facing a complex situation of SQL queries. The task is to update multiple rows, with multiple values and multiple conditions. Following is the data which I want to update; Field to update: 'sales', condition fields: 'campid' and 'date':

if campid = 259 and date = 22/6/2011 then set sales = $200
else if campid = 259 and date = 21/6/2011 then set sales = $210
else if campid = 260 and date = 22/6/2011 then set sales = $140
else if campid = 260 and date = 21/6/2011 then set sales = $150

I want to update all these in one query.

Solution 1

Try this:

UPDATE your_table SET sales = 
CASE 
    WHEN campid = 259 AND date = 22/6/2011 THEN 200
    WHEN campid = 259 AND date = 21/6/2011 THEN 210
    WHEN campid = 259 AND date = 22/6/2011 THEN 140
    WHEN campid = 259 AND date = 21/6/2011 THEN 150
    ELSE sales
END

Naturally I don't know if date field is really DATE or DATETIME, so I left query showing what you can do, but maybe you have to fix dates comparison according to data type. If date field is DATE (as it should) you can write AND date = '2011-06-22' and so on.
Note ELSE condition: it's necessary to avoid records not falling inside other cases will be set to NULL.

Solution 2

Rather than write a sql query that is far too complicated and time involved, I believe you would be better off spending your time writing a data access object to handle these rather simple manipulations on a per record basis. This makes later maintenance of the code, along with development of new code using your data access objects far easier than a one time use, intricate sql query.

Solution 3

You certainly should not do these in a single query. Instead, if what you aim for is to update them atomically, all at the same time, you should issue several UPDATE statements in a single transaction.

You do not say which MySQL version you use, and not which storage engine. Assuming InnoDB - which is the standard in recent versions of MySQL and should generally be used for transactional systems - and also assuming you are doing this from the command line client, you would

mysql> set autocommit=0;
mysql> UPDATE ....;
mysql> UPDATE ....;
mysql> ...
mysql> commit;

You can then reenable autocommit if you like by repeating the first line, but with a value of 1:

mysql> set autocommit=1;

Comments

[ajreal]

imo, that's not answering what OP asking for

[Daniel Schneller]

I humbly disagree. The If-elseif-elseif etc. construct presented in the question, coupled with the statement that he wants to update fields all at the same time is exactly what my answer would do. Of course, there are other ways to do this, as well, if one statement is really(!) needed.

[Nadeem Jamali]

Daniel is correct. I was asking for multiple conditions for a certain execution.

[Nadeem Jamali]

I used this example in my problem, it worked. After a long time, I came on conclusion that this type of query gives best performance on small amount of data. In case if the table has millions of records, then updating single record at a time using primary key in parameter, saves a lot of time, hence produces the better performance.

[e-info128]

Thanks, I saved the night