Q. How to make multiple updates using a single query in mysql ?
A. Using CASE & WHEN commands to solve it.
As you might know it’s quite easy to make multiple INSERTs in a single query, like this:
INSERT INTO table_name (field1, field2) VALUES ('value1', 'value2'), ('value3', 'value4');
However, for some strange reason you can’t do multiple changes to a table in a single Update query like this:
UPDATE table_name SET (field2 = 'valuex' WHERE field1 = 'value1'), (field2='valuey' WHERE field1 = 'value3');
But, you can do a very interesting trick. You can combine an UPDATE with a CASE like this:
UPDATE table_name SET field2 = CASE WHEN field1 = 'value1' THEN 'valuex'; WHEN field1 = 'value3' THEN 'valuey'; ELSE field2 END;
The ELSE field2 is very important, otherwise you will overwrite the rest of the table with NULL.