Updating multiple rows with a conditional where clause in Postgres?

Question

I'm trying to update multiple rows in a single query as I have many rows to update at once. In my query, there is a where clause that applies only to certain rows.

For example, I've the following query:

update mytable as m set
    column_a = c.column_a,
    column_b = c.column_b
from (values
    (1, 12, 6, TRUE),
    (2, 1, 45, FALSE),
    (3, 56, 3, TRUE)
) as c(id, column_a, column_b, additional_condition)
where c.id = m.id
     and CASE c.additional_condition when TRUE m.status != ALL(array['active', 'inactive']) end;

The last line in the where clause (m.status != ALL(array['active', 'inactive'])) should only be applied to rows which has TRUE in the value of c.additional_condition. Otherwise, the condition should not be applied.

Is it possible to achieve this in Postgres?

Answer 1

I think that this is what you want:

and CASE  
  when c.additional_condition THEN m.status != ALL(array['active', 'inactive']) 
  else TRUE
end