Oracle 12 – Reset a sequence Column with row_number() over Partition

I have this table (with order by product, seq):

product     group       seq
10             5         1
10             11        5
11             2         3
11             4         4
11             24        5

As you can see because of some delete statments I need to reset the seq column and have a seq from 1 for every product/group like this:

productnr     group     seq
10             5         1
10             11        2
11             2         1
11             4         2
11             24        3

I tried to create a new counter with this code:

select t.*, row_number() over (partition by t.product order by  t.product ) newSeq
from table t
order by t.product, t.seq;

and the result was like this:

product     group     seq      newSeq
10             5         1         1
10             11        5         2
11             2         3         1
11             4         4         2
11             24        5         3

But when I try it with Update I get ora-00907:

update table a set a.seq= 
(
    select row_number() over (partition by z.product order by  z.product ) newSeq
    from table z
    where z.product = a.product and z.seq= a.seq
    order by z.product, z.seq
);

I think the error is because of “order by z.product, z.seq” in Subselect. I can not remove it, because the newSeq must be based on old Seq and without it the newSub might be wrong.

Update 1:

Hier is the example without orderby:

select t.*, row_number() over (partition by t.product order by  t.product ) newSeq
from table t
order by t.product, t.seq;

result:

product       group     seq        newSeq
10             11        5           1 
10             5         1           2
11             4         4           1
11             2         3           2
11             24        5           3

As you can see the newSeq is not based On old Seq without order by. It means when product 5/group 11 has a greather seq than the same product/group 5, the new Seq should be like that as well.

Answer

Use a MERGE statement and correlate on the ROWID pseudo-column to efficiently self-join:

MERGE INTO table_name dst
USING (
  SELECT ROWID AS rid,
         ROW_NUMBER() OVER (
           PARTITION BY product
           ORDER BY ROWNUM  -- or ORDER BY seq
         ) AS newSeq
  FROM   table_name
) src
ON ( dst.ROWID = src.rid )
WHEN MATCHED THEN
  UPDATE SET seq = src.newSeq;

(Note: You do NOT need an ORDER BY expression appended to the SELECT statement as it will have no practical effect as the order of the source query is irrelevant during the MERGE and could make the SELECT statement less efficient if the ordering is applied and the SQL engine does unnecessary work.)

Which, for the sample data:

CREATE TABLE table_name ( product, "GROUP", seq ) AS
SELECT 10,  5, 1 FROM DUAL UNION ALL
SELECT 10,  1, 5 FROM DUAL UNION ALL
SELECT 11,  2, 3 FROM DUAL UNION ALL
SELECT 11,  4, 4 FROM DUAL UNION ALL
SELECT 11, 24, 5 FROM DUAL

Then results in:

SELECT * FROM table_name

PRODUCT	GROUP	SEQ
10	5	1
10	1	2
11	2	1
11	4	2
11	24	3

db<>fiddle here