使用 ROW_NUMBER() 更新 SQL 记录 [英] SQL update records with ROW_NUMBER()

问题描述

我有一张名为cards"的表，其中有一列名为position"如何使用 ROW_NUMBER() 更新/设置位置"以等于每条记录的行号?

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()?

我可以使用以下语句查询记录并获得正确的值:

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.

推荐答案

让我假设 cards 有一个主键.然后就可以使用join:

Let me assume that cards has a primary key. Then you can use 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;

我应该注意到 over () 看起来很奇怪，但 Postgres 确实允许它.通常会包含 order by 子句.

I should note that the over () looks strange but Postgres does allow it. Normally an order by clause would be included.

这篇关于使用 ROW_NUMBER() 更新 SQL 记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！