SQL multiple SETs in one UPDATE?

Question

I have a SQL field like so:

FIELD_A  
  cat     
  dog 
  bird
  mole
  dog

I want to UPDATE

• all dog to pug
• all bird to owl
• all cat to angora.

Apparently, the SQL UPDATEstatement only allows one SET condition at a time.

How can I write a query to accomplish the above operation all at once?

Answer 1

UPDATE AnonymousTable
   SET Field_A = (CASE Field_A
                  WHEN 'dog'  THEN 'pug'
                  WHEN 'bird' THEN 'owl'
                  WHEN 'cat'  THEN 'angora'
                  ELSE Field_A END)
 WHERE Field_A IN ('dog', 'bird', 'cat'); 

With the WHERE clause, the ELSE clause in the CASE expression is optional or redundant - but including the ELSE gives you reliability. One of the more serious mistakes is not to cover that 'none of the above' alternative and find that everything that wasn't mentioned is set to NULL.

Answer 2

with CASE clause you can accomplish this. here an example

http://www.java2s.com/Code/SQLServer/Select-Query/UseCASEintheUPDATEstatement.htm