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.
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;