MySQL batch update statement with different values

Question

Is there any single SQL statement equivalent to these?

UPDATE table_name SET (a = 'something', b='B1') WHERE id=1;
UPDATE table_name SET (a = 'something else', b='B2') WHERE id=2;
UPDATE table_name SET (a = 'another', b='B3') WHERE id=3;

What's wrong with executing three separate statements? Unless you're seeing particularly high latency to your server, this shouldn't be an issue. — Michael Mior, Nov 21 '11 at 04:40

score 5 · Accepted Answer · answered Nov 21 '11 at 04:16

5

Yes, this:

UPDATE table_name
   SET a = CASE WHEN id = 1
                THEN 'something'
                WHEN id = 2
                THEN 'something else'
                WHEN id = 3
                THEN 'another'
           END
 WHERE id IN (1,2,3)
;

but I'm not sure if it's what you have in mind?

answered Nov 21 '11 at 04:16

ruakh

175,680
26
273
307

Thanks, what about multiple fields (see edited question), can we wrap fields within CASE block or do we need to write different CASE blocks for each field? My query is going to update 5 fields. Thanks again. – Moe Sweet Nov 21 '11 at 04:22
1

The OP also wants to update b, you will need a second `CASE` for that. – Adam Wenger Nov 21 '11 at 04:22
You would need 5 `CASE` statements for your 5 fields. – Adam Wenger Nov 21 '11 at 04:23
@MoeSweet: You'll need separate CASE expressions, since MySQL doesn't support the `SET (a, b) = (...)` syntax *and* doesn't support CASE expressions that return multiple values. – ruakh Nov 21 '11 at 04:33

score 2 · Answer 2 · edited May 23 '17 at 11:44

2

If you have bigger array of data to be inserted, then you may use ON DUPLICATE KEY UPDATE construction. It will work more efficient in MySQL.

See my answer here for the similar question, for the example of usage.

edited May 23 '17 at 11:44

Community

1
1

answered Mar 02 '16 at 09:53

Farside

9,923
4
47
60

score 0 · Answer 3 · answered Mar 26 '20 at 05:36

You can use select rows with your constant value, then join it with you table.

UPDATE table_name T INNER JOIN (select 1 as id,'something' as a,'B1' as b
union all select 2,'something','B2'
union all select 3,'another','B2') V on T.id = V.id set T.a = V.a, T.b = V.b;

Refer to How to select multiple rows filled with constants?

MySQL batch update statement with different values

3 Answers3