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.

Improve this question
5
  • What sort of trouble do you have? Commented Jul 31, 2017 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. Commented Jul 31, 2017 at 11:53
  • Since you are joining with column_b, why should it matter whether column_a is unique or not? Commented Jul 31, 2017 at 11:56
  • I am sorry, I mistakenly typed column_a.. I edited the question. Commented Jul 31, 2017 at 11:58
  • What's the primary key of the table? Can you edit the question and add the CREATE TABLE? Commented Jul 31, 2017 at 12:48

1 Answer 1

Reset to default
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 ;
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.