how can i update a table using an field?

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;

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top