hi everyone,
i have the following data in a table
with maindata as (
select 111 pid, null seq from dual union all
select 222 pid, null seq from dual union all
select 333 pid, null seq from dual union all
select 111 pid, null seq from dual union all
select 222 pid, null seq from dual union all
select 444 pid, null seq from dual union all
select 555 pid, null seq from dual union all
select 444 pid, null seq from dual
)
i have a sequence that i created that starts from number 1. i want to source pid column in asc order and update seq column using the sequence
when i write the following update statement it failed
update maindata set = seq.nextval
order by pid
can someone help write an update query that will order the data by pid and then assign a seq and update seq column.
the table should should look like
pid seq
111 1
111 2
222 3
222 4
333 5
444 6
444 7
555 8