Update records that satisfies a condition with incrementing number

postgresql, sql

Solution

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;

Problem

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 ``` 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 ``` 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?

Original source