How do I do multiple updates in a single SQL query?

Question

Okay, here's the case: I have an SQL query like this:

UPDATE foo
SET flag=true
WHERE id=?

Now, I have a PHP array, which has a list of IDs. Is there a neat way of doing this, outside parsing something like:

foreach($list as $item){  
    $querycondition = $querycondition . " OR " . $item;  
}

and using that in the where clause?

Answer 1

You should be able to use the IN clause (assuming your database supports it):

UPDATE foo SET flag=true WHERE id in (1, 2, 3, 5, 6)

Answer 2

I haven't ever seen a way to do that other than your foreach loop.

But, if $list is in any way gotten from the user, you should stick to using the prepared statement and just updating a row at a time (assuming someone doesn't have a way to update several rows with a prepared statement). Otherwise, you are wide open to sql injection.

Answer 3

Use join/implode to make a comma-delimited list to end up with:

UPDATE foo SET flag=true WHERE id IN (1,2,3,4)

Answer 4

This would achieve the same thing, but probably won't yield much of a speed increase, but looks nicer.

mysql_query("UPDATE foo SET flag=true WHERE id IN (".implode(', ',$list).")");

Answer 5

you can jam you update with case statements but you will have to build the query on your own.

UPDATE foo
SET flag=CASE ID WHEN 5 THEN true ELSE flag END  
    ,flag=CASE ID WHEN 6 THEN false ELSE flag END 
WHERE id in (5,6)

The where can be omitted but saves you from a full table update.

Answer 6

VB.NET code: dim delimitedIdList as string = arrayToString(listOfIds)

dim SQL as string = " UPDATE foo SET flag=true WHERE id in (" + delimitedIdList + ")"

runSql(SQL)

Answer 7

Use IN statement. Provide comma separated list of key values. You can easily do so using implode function.

UPDATE foo SET flag = true WHERE id IN (1, 2, 3, 4, 5, ...)

Alternatively you can use condition:

UPDATE foo SET flag = true WHERE flag = false

or subquery:

UPDATE foo SET flag = true WHERE id IN (SELECT id FROM foo WHERE .....)

Answer 8

If you know a bound on the number of items then use the "IN" clause, as others have suggested:

UPDATE foo SET flag=true WHERE id in (1, 2, 3, 5, 6)

One warning though, is that depending on your db there may be a limit to the number of elements in the clause. Eg oracle 7 or 8 (?) used to have a limit of 256 items (this was increased significantly in later versions)
If you do iterate over a list use a transaction so you can rollback if one of the updates fails