MySQL – How to Update Multiple Rows in a Single Query with Multiple WHERE Clauses

MySQL

How can I simplify these Queries

UPDATE `table` SET `col1` = 'abc', `col2` = 'xyz' WHERE `col3` = '1';
UPDATE `elbat` SET `col1` = `a`, `col2` = 'x' WHERE `col3` = '1';
UPDATE `elbat` SET `col1` = `b`, `col2` = 'y' WHERE `col3` = '2';
UPDATE `elbat` SET `col1` = `c`, `col2` = 'z' WHERE `col3` = '3';

Into something like this?

UPDATE `table` a, `elbat` b 
SET a.`col1` = 'abc', a.`col2` = 'xyz' WHERE a.`col3` = '1',
SET b.`col1` = `a`, b.`col2` = 'x' WHERE b.`col3` = '1',
SET b.`col1` = `b`, b.`col2` = 'y' WHERE b.`col3` = '2',
SET b.`col1` = `c`, b.`col2` = 'z' WHERE b.`col3` = '3';

to run a Single-Query instead of Four-Queries

Best Answer

Remarks

It is possible to update rows based on some condition. It is also possible to update multiple tables in one statement in MySQL.

Whether the latter is a good idea is debatable, though. The target tables would be joined together for the update, and when I say "joined", I mean it in a broader sense: you do not have to specify a joining condition, in which case theirs would be a cross join. In a cross join, when at least one of the tables has more than one row, the other table will inevitably have its rows duplicated in the joined set. If both have multiple rows, both will have them multiplied. Somewhat counter-intuitively, MySQL will still update each affected row just once, yet I would refrain from multi-table updates in such scenarios, even if solely because of the counter-intuitiveness.

Method 1

Anyway, moving on to your specific example, there is indeed no joining condition, only a filter on each table. You can specify those filters in the WHERE clause of the UPDATE. Now in order to select which value to update each column with, you can use a CASE expression. This is what the complete UPDATE statement might look like:

UPDATE
  A, B
SET
  A.col1 = 'abc',
  A.col2 = 'xyz',
  B.col1 = CASE B.col3
             WHEN '1' THEN 'a'
             WHEN '2' THEN 'b'
             WHEN '3' THEN 'c'
           END,
  B.col2 = CASE B.col3
             WHEN '1' THEN 'x'
             WHEN '2' THEN 'y'
             WHEN '3' THEN 'z'
           END
WHERE
  A.col3 = '1'
  AND B.col3 IN ('1', '2', '3')
;

You can see that you have to repeat the same set of conditions in a CASE expression both for B.col1 and for B.col2. Is there a way to avoid that?

Method 2

Yes, there is. You can arrange the target values for B.col1 and B.col2 as well as the filtering values for B.col3 as a derived table and join it to B in the UPDATE clause, like this:

UPDATE
  A,
  B
    INNER JOIN
    (
      SELECT 'a' AS col1, 'x' AS col2, '1' AS col3
      UNION ALL
      SELECT 'b', 'y', '2'
      UNION ALL
      SELECT 'c', 'z', '3'
    ) AS fltr ON B.col3 = fltr.col3
SET
  A.col1 = 'abc',
  A.col2 = 'xyz',
  B.col1 = fltr.col1,
  B.col2 = fltr.col2
WHERE
  A.col3 = '1'
;

The join is also acting as a filter for B, so you can omit the one in the WHERE clause.

You can find a demo for each method at dbfiddle logo db<>fiddle:

Better way

Finally, as have been remarked both at the beginning of this post and in the comments, you can have a separate UPDATE statement for each table. The result would be clear in intention both to the reader of your script and to the database engine. A simpler script enables the latter to have more options for optimisation.

Use either of the methods above for the table B update, but do both tables separately:

UPDATE
  A
SET
  A.col1 = 'abc',
  A.col2 = 'xyz'
WHERE
  A.col3 = '1'
;

UPDATE
  B
    INNER JOIN
    (
      SELECT 'a' AS col1, 'x' AS col2, '1' AS col3
      UNION ALL
      SELECT 'b', 'y', '2'
      UNION ALL
      SELECT 'c', 'z', '3'
    ) AS fltr ON B.col3 = fltr.col3
SET
  B.col1 = fltr.col1,
  B.col2 = fltr.col2
;

There is also another reason for splitting the updates in separate statements. Since for a single UPDATE statement the tables need to be joined, it is important that both tables have rows intended for the update. If one table has no matching rows, then, even if the other does, neither will be updated. This is because an empty set cross-joined to a non-empty set still results in an empty set.

So, the single UPDATE statement would have no rows to work with if at least one table had no rows matching the condition(s). That would not happen with separate UPDATEs, because each would work with its own table regardless of the contents of the other, therefore, absence of rows in one table would not affect the update of the other.

Related Question