15

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?

Chrisissorry
  • 1,434
  • 2
  • 22
  • 43
Thomas
  • 962
  • 2
  • 9
  • 18
  • 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

2 Answers2

28

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 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
8

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;
Lev Buchel
  • 532
  • 5
  • 14