Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – UPDATE multiple rows and plus two row in one query

How can I update multiple rows in one query?

UPDATE table SET col_1=col_1+5, col_2=col_2+7 WHERE id=12;
UPDATE table SET col_1=col_1+2, col_2=col_2+6 WHERE id=16
...
UPDATE table SET col_1=col_1+..., col_2=col_2+... WHERE id=...

How to union following queries

Tags:

2

Answers


  1. 0

    Here’s a way to do it:

    WITH cte(id, col1, col2) AS (
  VALUES ROW(12, 5, 7), ROW(16, 2, 6), ROW(20, 1, 4)
)
UPDATE mytable
JOIN cte USING (id)
SET mytable.col1 = mytable.col1 + cte.col1,
    mytable.col2 = mytable.col2 + cte.col2;

    WITH and the VALUES statement requires MySQL 8.0.

    Demo: https://dbfiddle.uk/DQ_g6CZg

    Login or Signup to reply.
  2. 0

    If this is about considering the updates as an atomic action that shall be committed or rolled back, then wrap them in a transaction:

    START TRANSACTION;
UPDATE table SET col_1=col_1+5, col_2=col_2+7 WHERE id=12;
UPDATE table SET col_1=col_1+2, col_2=col_2+6 WHERE id=16
...
COMMIT;

    If this is about avoiding round trips by sending one combined statement to the server instead of single statements, one after the other, then combine them:

    UPDATE table 
SET col_1 = col_1 + CASE WHEN id = 12 THEN 5 WHEN id = 16 THEN 2 WHEN id = ... END
  , col_2 = col_2 + CASE WHEN id = 12 THEN 7 WHEN id = 16 THEN 6 WHEN id = ... END
WHERE id  IN (12, 16, ...);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search