Update multiple rows using one query

Can I update multiple rows using one query?开发者_Go百科

How to union following queries:

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

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

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.

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.

You can use a OR clause combined with the IF() function (or CASE WHEN... for other RDBMS)

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

Generally the only way you can update multiple rows in a single query is if your where clause matches multiple rows... and then every row will have the same values set.

Past that you can do funky stuff with expressions in your set clauses, but generally it's cleaner to do multiple queries, unless there's a very specific reason you can't.