CODEWITHSUNDEEP
sqlpostgresqljoinsql-update
Thomas
Thomas

Reputation: 1032

Update table using result of another query

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?

Upvotes: 15

Views: 31620

Answers (2)

Lev Buchel
Lev Buchel

Reputation: 592

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;

Upvotes: 8

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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;

Upvotes: 29

Related Questions