开发者

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.
开发者_StackOverflow中文版

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?


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');
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜