3

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

5
  • You can use the case statement. But are these tables related? I mean do they have a relation? Feb 18 '18 at 7:03
  • 1
    Your queries are not valid. .. SET col1 = 'abc' AND col2 = 'xyz' .. should be .. SET col1 = 'abc', col2 = 'xyz' .. Feb 18 '18 at 14:30
  • Why -- Performance? Clarity? Simplicity? All of those argue against a Single-Query.
    – Rick James
    Feb 23 '18 at 12:28
  • @RickJames I always believed that a Single-Query with JOINs will be faster than a Query for each Table of the JOINs.
    – Toleo
    Feb 25 '18 at 3:42
  • @Matheo - That belief is usually correct for SELECT. UPDATE is different than SELECT. A "cross join" is usually really bad for performance. Etc.
    – Rick James
    Feb 25 '18 at 13:42
4

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.

7
  • The two Methods seems promising, But looking at both of the, Method 1 seems more Optimal for Performance but requires a lot of writing, But the Method 2 is the opposite, Am I correct?
    – Toleo
    Feb 18 '18 at 14:50
  • 1
    @Matheo: not really. I suggest you use Method 2, but split the UPDATE into 2 statements, one for each table. Feb 18 '18 at 14:58
  • 1
    @Matheo: When there are many columns to update that would use the same set of conditions, Method 1 would definitely be more verbose than Method 2. As for the performance, I'm not sure. My guess is the difference, if any, would be negligible, but I don't have a MySQL environment for proper testing. And I second ypercubeᵀᴹ's suggestion about updating each table in its own statement.
    – Andriy M
    Feb 18 '18 at 15:00
  • 2
    I meant: use UPDATE tableA ...; in one statement and UPDATE tableB ...; in another. Don't update 2 tables in one statement. Feb 18 '18 at 15:02
  • 1
    @Matheo: That really is too broad a stroke. Combining complex updates in a single statement can easily result in a monster script that would be both slow to execute and difficult to understand/maintain. Also, while common-sense precautions are perfectly fine when speaking about performance, usually you should stick to simpler approaches, unless/until you've actually encountered problems with them.
    – Andriy M
    Feb 18 '18 at 15:15
0

You can use below one for one table if you want to update many columns of one table.

UPDATE table
SET col1 = CASE WHEN col3 = 'name1' THEN 'a' 
            WHEN col3 = '2' THEN b 
            ELSE 0 
       END
 , col2 = CASE WHEN col3 = '1' THEN 'b' 
           WHEN col3 = 'name2' THEN 'c' 
           ELSE '' 
      END
;
1
  • 1
    Correct about sung CASE but you are missing several details (like a WHERE clause) and the ELSE parts seem to be incorrect (you don't want the columns updated there). Feb 18 '18 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.