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?

Or with more details: stackoverflow.com/questions/13473499/… — Erwin Brandstetter
– Erwin Brandstetter, Commented May 21, 2015 at 22:23

Gordon Linoff · Accepted Answer · 2015-05-21 21:26:56Z

30

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, 2015 at 21:26

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Greer Over a year ago

I believe it needs to be from (select ...) as t1, without the "as" I get an error

Gordon Linoff Over a year ago

@MattGreer . . . Postgres definitely does not require as to declare table aliases. Perhaps you should ask a question for the database you are actually using.

Lev Buchel · Accepted Answer · 2019-07-11 07:15:01Z

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;

edited Jul 11, 2019 at 7:15

answered Jul 11, 2019 at 6:49

Lev Buchel

5926 silver badges15 bronze badges

3 Comments

Lev Buchel Over a year ago

Tested in mysql, can be helpful for people searching a solution not necessarily for Postgres.

user330315 Over a year ago

But the question is tagged with postgresql

Lev Buchel Over a year ago

I found this question by searching for a similar issue not specifically for postgresql, others may also find it useful.

Collectives™ on Stack Overflow

Update table using result of another query

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related