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.

  • What sort of trouble do you have? – dezso Jul 31 '17 at 11:51
  • The answer provided (dba.stackexchange.com/questions/69471/postgres-update-limit-1) uses a FROM clause, however my statement already has one and I don't know how to combine them. – Jens de Bruijn Jul 31 '17 at 11:53
  • Since you are joining with column_b, why should it matter whether column_a is unique or not? – Lennart Jul 31 '17 at 11:56
  • I am sorry, I mistakenly typed column_a.. I edited the question. – Jens de Bruijn Jul 31 '17 at 11:58
  • What's the primary key of the table? Can you edit the question and add the CREATE TABLE? – ypercubeᵀᴹ Jul 31 '17 at 12:48

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
         ('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 ;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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