Search code examples
sqlpostgresqljoinsql-update

How to use order by & limit on PostreSQL update join

I have two tables, A and B. A contains two new fields that I need to populate from B. A & B have a 1-to-many relationship so I need to use a ORDER BY and LIMIT 1 at the same time.

UPDATE a
SET x=b.x, y=b.y
FROM b
WHERE a.some_id=b.some_id
ORDER BY b.z ASC
LIMIT 1

I am getting the following error:

SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Solution

  • You need a subquery that picks the latest row from b for each a

    UPDATE a
  SET x=b.x, y=b.y
FROM (
  select distinct on (b.some_id) *
  from b
  order by some_id, some_timestamp desc --<< picks the latest
) b 
WHERE a.some_id = b.some_id