How to use order by & limit on PostreSQL update join-CodePudding

I am struggling to write a PostreSQL query. Let me explain what I'm trying to achieve:

I have two tables (lets name them A and B). I've modified table A to contain 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.

The query looks like this:

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

and I am getting the following error:

SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Any help would be greatly appreciated! Thanks in advance

CodePudding user response：

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