All geek questions in one place

SQL multiple SET in one UPDATE?

I have an SQL field like this:

FIELD_A cat dog bird mole dog

I want UPDATE

  • whole dog for a pug
  • all owl birds
  • all cat to angora.

Apparently, the SQL UPDATE allows only one SET clause at a time.

How can I write a request to perform the above operation right away?

+4
source share
3 answers
 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');

In the WHERE clause, the ELSE clause in the CASE expression is optional or redundant - but including ELSE gives you reliability. One of the most serious mistakes is not to cover this “none of the above” alternatives and find that everything that was not mentioned is set to NULL.

+11
source

with a CASE clause you can do it. here is an example

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

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

Source: https://habr.com/ru/post/1342087/

More articles:


All Articles