Update multiple rows with multiple values and multiple conditions mysql

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.

Answers


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.


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.


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;

Need Your Help

Learning OpenGL ES 1.x

iphone 3d opengl-es

What is the quickest way to come up to speed on OpenGL ES 1.x?

Add constraint not listed in Editor menu in Xcode

ios xcode nslayoutconstraint

I am trying to add layout constraints to my NIB, and i am following the directions apple provides here: