CODEWITHSUNDEEP
mysqlsql
Max Williams
Max Williams

Reputation: 32943

MySQL - update groups of records in one hit

lets's say that i have a table users and i want to set the foo field to "bar" for users with id in (1,5,10) and to "qux" for users with id in (3,8,15), and that generally i might have more values and more groups of ids to go with each value.

Currently (in my scripting language of choice) i'm looping over the pairs of values of foo and groups of ids, like this (pseudocode)

for(val, ids) in my_array
  execute_sql("update users set foo = '#{val}' where id in (#{ids.join(',')})")
end

Is there a way i can do this in a single sql command, instead of one command per pair?

Upvotes: 2

Views: 53

Answers (2)

jarlh
jarlh

Reputation: 44776

Use CASE to chose update value:

update users
  set foo = case when id in (1,5,10) then 'bar'
                 when id in (3,8,15) then 'qux'
                 else foo end

where id in (1,5,10) or id in (3,8,15)

The WHERE clause is there to keep transaction size down, but doesn't affect the result of the UPDATE.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can do this in SQL by using the case statement. The query would look like:

update users u
    set foo = (case when id in (1, 5, 10) then 'bar'
                    when id in (3, 8, 15) then 'qux'
                    else foo
               end)
    where id in (1, 5, 10) or id in (3,  8, 15);

Note: the else is not strictly needed, so long as the lists in the where and when statements match. But it is safer.

Also, the in lists in the where could be combined into one. However, I think it is clearer because this matches the case statement logic.

Upvotes: 3

Related Questions