PostgreSQL – How to Update Multiple Rows in the Same Query with Limit

postgresqlpostgresql-9.6

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.

Best Answer

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