Multiple Updates


I have three separate UPDATE command but I want them as a single command.

Update `backgroundFiles` SET dimension = "1024x768" where medProfID=4172
Update `backgroundFiles` SET dimension = "1620x1200" where medProfID=4173
Update `backgroundFiles` SET dimension = "1280x1024" where medProfID=4174

How can I combine above three command in a single UPDATE command?


UPDATE backgroundFiles 
   SET dimension = 
       CASE WHEN medProfID = 4172
            THEN '1024x768'
            WHEN medProfID = 4173
            THEN '1620x1200'
            WHEN medProfID = 4174
            THEN '1280x1024'
            ELSE NULL END
 WHERE medProfID IN ( 4172,4173,4174 )

Thank you r937,

It is working but I did not understand why we need to specify again the WHERE values as an another array although they were used in conditionals?

WHERE medProfID IN ( 4172,4173,4174 )

the WHERE clause is vitally important

try leaving it off and you will see why

tip: back up your table first