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?

The last line in the where clause (m.status = 'active') ... there is no m.status = 'active' but m.status != ALL(array['active', 'inactive']). — forpas
– forpas, Commented Sep 29, 2019 at 17:33

forpas · Accepted Answer · 2019-09-29 17:35:50Z

2

I think that this is what you want:

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

answered Sep 29, 2019 at 17:35

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hooman Bahreini · Accepted Answer · 2019-10-10 04:47:27Z

1

I think the logic you want is:

where c.id = m.id and
      ( (not c.additional_condition) and orm.status = 'active' )

You can use in or arrays for multiple values:

where c.id = m.id and
      ( (not c.additional_condition) and orm.status not in ( 'active', 'inacctive') )

I don't see a particular value to use arrays, unless you are passing a value in as an array.

edited Oct 10, 2019 at 4:47

Hooman Bahreini

15.7k11 gold badges87 silver badges158 bronze badges

answered Sep 29, 2019 at 17:18

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Carven Over a year ago

I'm getting an error that says ERROR: syntax error at or near "(". I've having something that looks like this: where... and ( (not c.additional_condition) (m."status") != ALL(array['active', 'inactive']) )

Collectives™ on Stack Overflow

Updating multiple rows with a conditional where clause in Postgres?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related