-1

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

philipxy
  • 14,867
  • 6
  • 39
  • 83
nlopez
  • 351
  • 1
  • 13
  • 1
    So which row from `b` should be picked if there are multiple matches for a single row in `a`? (I also don't think that the query you have shown will result in the error you have shown) –  Aug 19 '22 at 11:53
  • 1
    Well even _if_ the UPDATE statement did support `order by` and `limit` (which it doesn't) the it would apply to the _result_ of the join. Not to each row individually –  Aug 19 '22 at 13:13
  • Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) – philipxy Aug 19 '22 at 21:05
  • Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Aug 19 '22 at 21:06

1 Answers1

1

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