I have table like this:
Incident_id is foreign key. What I want to achieve it -> create one more column with named position (int). And populate it like this: find all rows for each incident_id and and update each row with index or list of rows I get by each incident_id. exapmple: incident_id 5 matches with 4 note rows so updated for the position will be 0, 1, 2, 3 accordingly. Ty
I would not recomment storing such derived information. Instead, you can create a view that uses row_number() to enumerate the rows of each incident_id:
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.
EDIT
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;