How to update multiple columns with different where clauses in mySQL

Question

im trying to update multiple columns in a table that all needs different where clauses in single query. Im not amazing at sql, and struggle to find help on google. im trying to achieve something like this

UPDATE $table 
SET meta_value = 'john',
    meta_value = 'v1234'
WHERE   meta_key = 'name' AND post_id=$post_id,
        meta_key = 'trak' AND post_id=$post_id"

the idea being that the first SET matches the first WHERE, and the 2nd SET matches the 2nd WHERE etc.. ideally id be able to do many of these. any idea how I can achieve this?

Similar to: http://stackoverflow.com/questions/29019317/update-multiple-rows-mysql/29019502#29019502 — xQbert, Mar 13 '15 at 19:46

xQbert · Accepted Answer · 2015-03-13T20:36:10.073

4

Update $table
set meta_value = CASE WHEN meta_key = 'name' then 'john'
                      WHEN meta_key = 'trak' then 'v1234' end
where post_Id = $post_ID

edited Mar 13 '15 at 20:36

answered Mar 13 '15 at 19:46

xQbert

34,733
2
41
62

1

Performance won't be good without a where clause. And not sure what it will do to the other values . – symcbean Mar 13 '15 at 19:47
Is it possible to add a where clause with just the id, since thats always the same here? – Malibur Mar 13 '15 at 20:13
would that be added after the 'end' ? – Malibur Mar 13 '15 at 20:21
Updated, yes and removed the unneeded multi conditional case statement – xQbert Mar 13 '15 at 20:36
Thanks a lot. It works! would this be inefficient if I have 50 WHEN/THEN pairs? – Malibur Mar 13 '15 at 20:45

score 1 · Answer 2 · answered Mar 13 '15 at 19:45

1

UPDATE $table 
SET meta_value = IF(meta_key='name','john','v1234')
WHERE   post_id=$post_id AND
    meta_key IN ('trak','name')

answered Mar 13 '15 at 19:45

symcbean

47,736
6
59
94

This seems to work, thanks! Would you be able to explain it a little bit. What does the expressions in the IF clause mean exactly? And how is this for performance if Im updating 50 different meta_keys? – Malibur Mar 13 '15 at 19:52
ahh. just realised that this would only work if its just 2 columns. But I have 500 columns that I'd like to update in one go – Malibur Mar 13 '15 at 19:59
Really? 500 *columns*? Then your schema needs fixed. – symcbean Mar 13 '15 at 20:02
ups that was suppose to 50 values not 500 columns, sorry – Malibur Mar 13 '15 at 20:17

How to update multiple columns with different where clauses in mySQL

2 Answers2