Update table using result of another query

Question

I have the following query that works fine

SELECT RecordID, ROW_NUMBER() OVER (ORDER BY (Value1) DESC) AS Rank
FROM Table1

Also, I have another table(table2) that contains (among others) the fields RecordID and Rank. I would like to update RecordID and Rank in table2 based on result of query above. Is that possible?

This might Help you http://stackoverflow.com/questions/1746125/update-columns-values-with-column-of-another-table-based-on-condition — Garry, May 21 '15 at 21:25
Or with more details: http://stackoverflow.com/questions/13473499/update-a-column-of-a-table-with-a-column-of-another-table-in-postgresql/13473660 — Erwin Brandstetter, May 21 '15 at 22:23

score 26 · Accepted Answer · answered May 21 '15 at 21:26

26

Yes, you can have multiple tables in an update in Postgres:

update table2
    set rank = t1.rank
    from (SELECT RecordID, ROW_NUMBER() OVER (ORDER BY (Value1) DESC) AS Rank
          FROM Table1
         ) t1
    where table2.RecordId = t1.RecordId;

answered May 21 '15 at 21:26

Gordon Linoff

1,198,228
53
572
709

Excellent! Thank you very much! – Thomas May 21 '15 at 21:32
1

I believe it needs to be `from (select ...) as t1`, without the "as" I get an error – Matt Greer Jun 10 '18 at 17:46
1

@MattGreer . . . Postgres definitely does not require `as` to declare table aliases. Perhaps you should ask a question for the database you are actually using. – Gordon Linoff Jun 10 '18 at 18:55

Lev Buchel · Answer 2 · 2019-07-11T07:15:01.797

7

What worked for me (in mysql) was :

update table2, (SELECT RecordID, ROW_NUMBER() OVER (ORDER BY (Value1) DESC) AS Rank 
    FROM Table1) tempTable 
set table2.Rank = tempTable.Rank 
where table2.RecordId = tempTable.RecordId;

edited Jul 11 '19 at 07:15

answered Jul 11 '19 at 06:49

Lev Buchel

484
5
12

1

Tested in mysql, can be helpful for people searching a solution not necessarily for Postgres. – Lev Buchel Jul 11 '19 at 06:58
But the question is tagged with `postgresql` – a_horse_with_no_name Jul 11 '19 at 07:02
1

I found this question by searching for a similar issue not specifically for `postgresql`, others may also find it useful. – Lev Buchel Jul 11 '19 at 07:14

Update table using result of another query

2 Answers2