UPDATING THE TABLE USING OTHER RESULTS

Question

I have the following query that works great

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

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

Answer 1

Yes, you can have several tables updatein 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;

Answer 2

What worked for me (in MySQL):

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;