postgresql 使用 ROW_NUMBER() 更新 SQL 记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41069860/
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
SQL update records with ROW_NUMBER()
提问by John.Mazzucco
I have a table called 'cards', which has a column called 'position' How can I update/set the 'position' to equal the row number of each record, using ROW_NUMBER()?
我有一个名为“卡片”的表，其中有一列名为“位置”的列如何使用 ROW_NUMBER() 更新/设置“位置”以等于每条记录的行号？
I am able to query the records and get the correct values using this statement:
我可以使用以下语句查询记录并获得正确的值：
"SELECT *, ROW_NUMBER() OVER () as position FROM cards"
So, I would like to do this but have it update the new values in the database.
所以，我想这样做，但让它更新数据库中的新值。
回答by Gordon Linoff
Let me assume that cardshas a primary key. Then you can use join:
让我假设它cards有一个主键。然后你可以使用join：
update cards c
set position = c2.seqnum
from (select c2.*, row_number() over () as seqnum
from cards c2
) c2
where c2.pkid = c.pkid;
I should note that the over ()looks strange but Postgres does allow it. Normally an order byclause would be included.
我应该注意到over ()看起来很奇怪，但 Postgres 确实允许它。通常order by会包含一个子句。
回答by Lukasz Szozda
Original question was tagged with SQLite.
Starting from SQLite 3.25.0we could natively use ROW_NUMBER.
原始问题被标记为SQLite。从SQLite 3.25.0开始，我们可以在本地使用ROW_NUMBER.
CREATE TABLE cards(pk INT PRIMARY KEY, c VARCHAR(2), seq INT);
INSERT INTO cards(pk, c) VALUES (10,'2?'),(20,'3?'),(30, '4?');
WITH cte AS (SELECT *, ROW_NUMBER() OVER() AS rn FROM cards)
UPDATE cards SET seq = (SELECT rn FROM cte WHERE cte.pk = cards.pk);
SELECT * FROM cards;
Exactly the same code will work with PostgreSQL too: Rextester Demo
完全相同的代码也适用于 PostgreSQL：Rextester Demo
