Because I just read the post titled “Leveraging Your SQL - Update Using a Join” over at Coldfusion Muse I figured I would add this post in an effort to help expand our SQL Updating skills. Sometimes, particularly with update scripts, I might need to update a bunch of rows in the same table to have different values based on some other value in their row. One way to do this is to write a bunch of different updates (one per row) OR you can write one update with a CASE statement in it.
UPDATE user SET confirmed_ind = CASE data_release WHEN 0 THEN 1 /* if no consent, we don't care about the 2006 confirmation */ WHEN 1 THEN 0 /* if prior consent given, user must confirm that a 2006 consent is registered. */ END;
You can even do much more complicated things as well:
UPDATE user SET type_id = CASE payrate WHEN 0 THEN 125 WHEN 1 THEN 150 WHEN 2 THEN 175 WHEN 3 THEN 150 WHEN 4 THEN 125 WHEN 5 THEN 200 WHEN 6 THEN 125 WHEN 7 THEN 250 END;
In that case you can a different SET clause for every column with their own conditions
Is there a way to use a CASE statement in an Update to change the column that is being updated?