Update Records That Satisfies A Condition With Incrementing Number

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 d

Solution 1:

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(orderby id) as rn from T where local_site_id=2) s
 where T.id=s.id;

Solution 2:

I believe this should do what you want, let me know otherwise:

UPDATE table_name
SET    local_id =rowFROM
(
    SELECT*, 
           ROW_NUMBER() OVER (PARTITIONBY local_site_id ORDERBY name) ASrowFROM   table_name
    WHERE  local_site_id =2
) AS Q
WHERE Q.id = table_name.id;