3

I am trying to update multiple rows in a table by using the following statement:

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where c.column_b = t.column_b;

However, in my database the values in column_b are not unique (e.g., multiple rows can have the '123'). I also have a column_c with a DATE type. For each of the rows in the update statement, I only want the update stated above to happen on the row with the most recent date in column_c, for example by ordering the data by date and using LIMIT 1.

Therefore, I am trying to combine this query with the answer provided here. However, I have trouble doing this.

5

1 Answer 1

5

You can use a derived table or cte to find one row (the latest) per column_b:

with upd as
( select distinct on (t.column_b) 
      t.pk, c.column_a              -- pk : the primary key column(s)
  from test as t
    join
      (values
         ('123', 1),
         ('345', 2)  
      ) as c (column_b, column_a) 
    on c.column_b = t.column_b
  order by t.column_b, t.date desc
) 
update test as t 
set column_a = upd.column_a
from upd
where upd.pk = t.pk ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.