SQL 使用另一个查询的结果更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30384584/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update table using result of another query
提问by Thomas
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?
此外，我还有另一个表（table2），其中包含（除其他外）RecordID 和 Rank 字段。我想根据上面的查询结果更新 table2 中的 RecordID 和 Rank。那可能吗？
回答by Gordon Linoff
Yes, you can have multiple tables in an updatein Postgres:
是的，您可以update在 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;
回答by Lev Buchel
What worked for me (in mysql) was :
对我有用的（在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;
