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

user_3935035 0
Viewed 28

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?

postgresql sql
2 Answers

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

user_22753880

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;
user_3303150
Related