SQL – setting and resetting in one query

A colleague has a web application where a number of flags can be set or reset using checkboxses. These flags corresponds to rows in a table. When they have been altered, those who are set are returned from the browser, whereas those that are not set are not mentioned.

The obious (imo) way of doing this is first to reset everyting:

update settings set flag=0

then set those returned with e.g.

update settings set flag=1 where id in(2,3,4,7)

but it is really possible to do it all in one go, using the sql if-function:

update settings set flag=if(id in(2,3,4,7),1,0)

This will set those with id in the in-clause to 1, all others to 0.

This entry was posted in sql. Bookmark the permalink.