Update multiple rows using one query

Question

Can I update multiple rows using one query?

How to union following queries:

UPDATE tablename SET col1='34355' WHERE id='2'

UPDATE tablename SET col1='152242' WHERE id='44'

Answer 1

You can use a virtual map table for this update.

update tablename
inner join (
    select '34355'  col1, '2'  id union all
    select '152242' col1, '44' id
) map on map.id = tablename.id
set tablename.col1 = map.col1

Using this pattern allows for easy expansion (just add rows to the map). It also allows MySQL to more predictably choose an index on tablename.id for the normal JOIN operation.

Answer 2

Can you? Sure. Should you? No way.

Think about the person looking at your code in five years. What's more readable, this:

UPDATE tablename SET col1='34355' WHERE id='2';
UPDATE tablename SET col1='152242' WHERE id='44';

or this (The Scrum Meister's answer):

UPDATE tablename SET col1 = IF(id='2', '34355','152242') WHERE id='2' OR id='44';

The second one is shorter, but it's a challenge to figure out exactly what it's doing. If you're worried about race conditions, make it a single transaction (in most modern DBMS):

BEGIN;
UPDATE tablename SET col1='34355' WHERE id='2';
UPDATE tablename SET col1='152242' WHERE id='44';
COMMIT;

That way you can be guaranteed no other query will run when row 2 is updated but row 44 is not.