CODEWITHSUNDEEP
sqlsql-update
Ben
Ben

Reputation: 57287

SQL multiple SETs in one UPDATE?

I have a SQL field like so:

FIELD_A  
  cat     
  dog 
  bird
  mole
  dog

I want to UPDATE

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?

Upvotes: 5

Views: 12919

Answers (3)

swissben
swissben

Reputation: 1077

UPDATE table_a
   SET field_a =
          DECODE (field_a,  'dog', 'pug',  'bird', 'owl',  'cat', 'angora')
 WHERE field_a IN ('dog', 'bird', 'cat');

Upvotes: 2

Jonathan Leffler
Jonathan Leffler

Reputation: 754570

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.

Upvotes: 12

adt
adt

Reputation: 4360

with CASE clause you can accomplish this. here an example

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

Upvotes: 3

Related Questions