I would not recomment storing such derived information. Instead, you can create a view that uses
row_number() to enumerate the rows of each
create view myview as select t.*, row_number() over(partition by incident_id order by id) - 1 rn from mytable t
To get a stable result, you need a column that can be used to consistently order the rows of each incident: I called it
id in the query. You can change that to the relevant column name (or set of columns) for your use case; you would typically use the primary key column(s) of your table.
If you really wanted to materialize that value in a new column, and considering that the primary key of your table, you would do:
alter table mytable add column position int; update mytable t set position = t1.position from ( select incident_note_id, row_number() over(partition by incident_id order by incident_note_id) - 1 position from mytable ) t1 where t1.incident_note_id = t.incident_note_id;
CLICK HERE to find out more related problems solutions.