CODEWITHSUNDEEP
sqlpostgresql
four-eyes
four-eyes

Reputation: 12439

More than one SET ... WHERE clause in UPDATE TABLE

Is it possible to have something like this

UPDATE TABLE foo
    SET bar = 1
    WHERE foo_bar = 'cat';

UPDATE TABLE foo
    SET bar = 2
    WHERE foo_bar = 'dog';

UPDATE TABLE foo
    SET bar = 3
    WHERE foo_bar = 'elephant';

in one statement?

Upvotes: 1

Views: 37

Answers (2)

Jim Jones
Jim Jones

Reputation: 19653

Use a CASE

UPDATE foo SET foo_bar = 
  CASE 
    WHEN bar = 1 THEN 'dog' 
    WHEN bar = 2 THEN 'cat'
    WHEN bar = 3 THEN 'elephant'
  END;

Demo: db<>fiddle

Upvotes: 1

user330315
user330315

Reputation:

You can use a CASE expression

update foo
  set bar = case foo_bar
              when 'cat' then 1
              when 'dog' then 2
              when 'elephant' then 3
            end
where foo_bar in ('cat', 'dog', 'elephant');

Alternatively this could be done using a VALUES clause to avoid repeating the values.

update foo
   set bar = t.new_bar
from t
  values 
    ('cat', 1),
    ('dog', 2),
    ('elephant', 3)
) as t(foobar, new_bar)
where t.foobar = foo.foo_bar;

Upvotes: 0

Related Questions