Postgres - Update multiple rows in the same query with limit

Question

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.

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
– Jens de Bruijn, 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? — Lennart - Slava Ukraini
– Lennart - Slava Ukraini, Commented Jul 31, 2017 at 11:56
I am sorry, I mistakenly typed column_a.. I edited the question. — Jens de Bruijn
– Jens de Bruijn, 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? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 31, 2017 at 12:48

ypercubeᵀᴹ · Accepted Answer · 2017-07-31 12:54:57Z

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 ;

answered Jul 31, 2017 at 12:54

ypercubeᵀᴹ

100k13 gold badges217 silver badges306 bronze badges

Add a comment |

Stack Exchange Network

Postgres - Update multiple rows in the same query with limit

1 Answer 1

Your Answer

Linked

Hot Network Questions

Postgres - Update multiple rows in the same query with limit

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions