UPDATE based on multiple “WHERE IN” conditions

Let’s say I have a table I want to update based on multiple conditions. Each of these conditions is an equal-sized array, and the only valid cases are the ones which match the same index in the arrays.

That is, if we use the following SQL clause

UPDATE Foo
SET bar = 1
WHERE a IN ( 1,   2,   3,   4,   5)
  AND b IN ( 6,   7,   8,   9,   0)
  AND c IN ('a', 'b', 'c', 'd', 'e')

bar will be set to 1 for any row which has, for example, a = 1, b = 8, c = 'e'.

That is not what I want.

I need a clause where only a = 1, b = 6, c = 'a' or a = 2, b = 7, c = 'b' (etc.) works.

Obviously I could rewrite the clause as

UPDATE Foo
SET bar = 1
WHERE (a = 1 AND b = 6 AND c = 'a')
   OR (a = 2 AND b = 7 AND c = 'b')
   OR ...

This would work, but it’s hardly extensible. Given the values of the conditions are variable and obtained programmatically, it’d be far better if I could set each array in one place instead of having to build a string-building loop to get that WHERE call right.

So, is there a better, more elegant way to have the same behavior as this last block?

Answer

You can use values() and join:

UPDATE f
    SET bar = 1
    FROM Foo f JOIN
         (VALUES (1, 6, 'a'),
                 (2, 7, 'b'),
                 . . .
         ) v(a, b, c)
         ON f.a = v.a AND f.b = v.b AND f.c = v.c;

Advertisement

Answer