postgresqlplpgsql

sql for each district increment column facility

I am trying to update a column [facility_id] with incrementing integer for each group of districts. The facility_id needs to start at 1 and end at x pending how many columns each district has. I have been playing with loops all day but I have nothing that works and dread doing this by hand because I have 3,000 rows to manipulate.

bad table

good table

I am new and still learning, please teach. Thank you!

Solution

  • Use row_number() window function to update the column:

    update tablename 
set Facility_ID = t.rn
from (
  select id, row_number() over (partition by District order by ID) rn
  from tablename
) t
where t.ID = tablename.ID

    See the demo.