SQL multiple SETs in one UPDATE?
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 UPDATE
statement only allows one SET
condition at a time.
How can I write a query to accomplish the above operation all at once?
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.
with CASE clause you can accomplish this. here an example
http://www.java2s.com/Code/SQLServer/Select-Query/UseCASEintheUPDATEstatement.htm
UPDATE table_a
SET field_a =
DECODE (field_a, 'dog', 'pug', 'bird', 'owl', 'cat', 'angora')
WHERE field_a IN ('dog', 'bird', 'cat');
精彩评论