[FIXED] How to use order by & limit on PostreSQL update join

Issue

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

Answered By - a_horse_with_no_name

Answer Checked By - Cary Denson (PHPFixing Admin)