Update two different rows in one line of SQL

mysql, sql, sql-update

Solution

You can execute one `UPDATE` with the use of `IF` (which mysql supports) or by using `CASE` to make it more RDBMS friendly.

UPDATE  example
SET     def = IF(abc = 1, 'foo', 'bar')
WHERE   abc IN (1, 2) -- reason to make it more faster, doesn't go on all records

OR

UPDATE  example
SET     def = CASE WHEN abc = 1 THEN 'foo' ELSE 'bar' END
WHERE abc IN (1, 2) -- reason to make it more faster, doesn't go on all records

Problem

Say I have a table called example as: [abc] | [def] --1---|-qwerty- --2---|-asdf--- What I am wanting to do is update both columns in one SQL query (using only one UPDATE). ``` UPDATE example SET def = 'foo' where abc = '1' UPDATE example SET def = 'bar' where abc = '2' ``` The above is what I am wanting to achieve but in one line of sql (using MySQL). I know you can do this like `UPDATE example SET def 'foo', SET def = 'bar'` but I'm not sure how you can do this with two different where statements.

Original source