用数字递增更新满足条件的记录 [英] Update records that satisfies a condition with incrementing number

问题描述

我在postgres中有一个这样的表：

I have a table in postgres like this:

Id    Name    local_site_id    local_id
1     A       2                
2     B       2
3     C       1
4     D       2
5     E       1

如何使用SQL查询将表更新为该表：

How do I update the table into this using SQL query:

Id    Name    local_site_id    local_id
1     A       2                1
2     B       2                2
3     C       1                
4     D       2                3
5     E       1                

现在，所有记录的local_id字段为空。我只想对具有 local_site_id = 2 的行使用从1开始的递增数字更新local_id值，是否可以使用SQL？

Right now, the local_id field is empty for all the records. I want to update the local_id values with an incrementing number starting from 1 only for rows that have local_site_id=2 Is it possible using SQL?

推荐答案

这是 row_number（）窗口函数的典型用例。
假设您的主表为T，则此查询应与PostgreSQL 8.4或更高版本一起使用：

That's a typical use case for the row_number() window function. Assuming your main table is T, this query should work with postgresql 8.4 or newer:

update T set local_id=s.rn 
from (select id,row_number() over(order by id) as rn from T where local_site_id=2) s
 where T.id=s.id;

这篇关于用数字递增更新满足条件的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！