How To Make Multiple Updates Using A Single Query In MySQL

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.