Update number field in Oracle

There might be a better way, but this seems to work.

UPDATE tab1 a 
SET    seq = (SELECT seq 
              FROM   (SELECT acct, 
                             dept, 
                             Row_number() 
                               over ( 
                                 PARTITION BY dept 
                                 ORDER BY dept, acct) seq 
                      FROM   tab1) b 
              WHERE  a.acct = b.acct 
                     AND a.dept = b.dept); 

Select allOpen in new window

got the following error with quggested query.

cannot update ("tab1"."seq") to NULL

replaced Row_number() to rownum. got the following error

                               over(
                                   *
ERROR at line 6:
ORA-00923: FROM keyword not found where expected

That tells me that your SEQ column has a not null constraint and that either the ACCT or DEPT column has a NULL in it (or both columns).  What do you want to do in that case?  The easiest thing to do in that case is to default it to 1, but if you have multiple NULLs with the same ACCT/DEPT, they would all get a 1.

UPDATE tab1 a 
SET    seq = Nvl((SELECT seq 
                  FROM   (SELECT acct, 
                                 dept, 
                                 Row_number() 
                                   over ( 
                                     PARTITION BY dept 
                                     ORDER BY dept, acct) seq 
                          FROM   tab1) b 
                  WHERE  a.acct = b.acct 
                         AND a.dept = b.dept), 1); 

Select allOpen in new window

all the records has seq=1 in the table now. No null values.

what does this mean?

SET    seq = Nvl((SELECT seq
                  *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

It may be helpful to provide a greater variety of sample data that includes nulls, duplicates, etc. plus the expected results from that data.