Update number field in Oracle

Greeting,

I have the following table in oracle
acct     dept   seq
001      555      1
002      555      1
003      555      1
004      666      1

How to update the seq field to get the following output?

acct     dept   seq
001      555      1
002      555      2
003      555      3
004      666      1
mrongAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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); 

Open in new window

0
mrongAuthor Commented:
got the following error with quggested query.

cannot update ("tab1"."seq") to NULL
0
mrongAuthor Commented:
replaced Row_number() to rownum. got the following error

                               over(
                                   *
ERROR at line 6:
ORA-00923: FROM keyword not found where expected
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johnsoneSenior Oracle DBACommented:
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); 

Open in new window

0
mrongAuthor Commented:
all the records has seq=1 in the table now. No null values.
0
mrongAuthor Commented:
what does this mean?

SET    seq = Nvl((SELECT seq
                  *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
0
johnsoneSenior Oracle DBACommented:
The current value in the SEQ field is irrelevant.  The fact that it is a NOT NULL column is relevant.

Given that you seem to have NULLs and duplicate records, what is your expected result for this set of data:
ACCT   DEPT   SEQ
----- ----- -----
001     555     1
002     555     1
003     555     1
004     666     1
005    NULL     1
006    NULL     1
NULL    777     1
NULL    777     1
NULL   NULL     1
009     888     1
009     888     1

Open in new window

This update will handle duplicates and should eliminate the ORA-1427, but you still haven't said what to do with NULLs.
UPDATE tab1 a 
SET    seq = Nvl((SELECT seq 
                  FROM   (SELECT rowid, acct, 
                                 dept, 
                                 Row_number() 
                                   over ( 
                                     PARTITION BY dept 
                                     ORDER BY dept, acct) seq 
                          FROM   tab1) b 
                  WHERE  a.rowid = b.rowid), 1); 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
It may be helpful to provide a greater variety of sample data that includes nulls, duplicates, etc. plus the expected results from that data.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.