SQL & PL/SQL

Afternoon all.

I have a table of accounts. Some of these accounts will have multiple account numbers associated to them - others will just have the one account number. The initial sequence number for each account is already set by another bit of code. There is a column in the table which details the next account number in the sequence - so by joining the table to itself, and starting at sequence number one - I have managed to create an accurate sequence of account numbers, for every account that we have.

The issue I have is that the script is quite long, and new account numbers can be added all the time. So the script needs to be maintained and added to when necessary. There are currently 65 steps in the update script below.

--- UPDATE 2ND SEQUENCE NUMBER
UPDATE ACCOUNT_LIFECYCLE
SET SEQUENCE = 2 WHERE ACCOUNT_NUMBER IN (SELECT B.ACCOUNT_NUMBER FROM ACCOUNT_LIFECYCLE A, ACCOUNT_LIFECYCLE B WHERE A.SEQUENCE = 1 AND A.NEXT_ACC_NO=B.ACCOUNT_NUMBER)
/

--- UPDATE 3ND SEQUENCE NUMBER
UPDATE ACCOUNT_LIFECYCLE
SET SEQUENCE = 3 WHERE ACCOUNT_NUMBER IN (SELECT B.ACCOUNT_NUMBER FROM ACCOUNT_LIFECYCLE A, ACCOUNT_LIFECYCLE B WHERE A.SEQUENCE = 2 AND A.NEXT_ACC_NO=B.ACCOUNT_NUMBER)

/
--- UPDATE 4TH SEQUENCE NUMBER
UPDATE ACCOUNT_LIFECYCLE
SET SEQUENCE = 4 WHERE ACCOUNT_NUMBER IN (SELECT B.ACCOUNT_NUMBER FROM ACCOUNT_LIFECYCLE A, ACCOUNT_LIFECYCLE B WHERE A.SEQUENCE = 3 AND A.NEXT_ACC_NO=B.ACCOUNT_NUMBER)

/

think this can be achieved in a small PL/SQL block, which would effectively loop through the iterations. I currently run the following query to check on the number of account numbers (to establish if I need to add more steps) - could this be defined as a bind variable??

select max(count(original_account_number)) from ACCOUNT_LIFECYCLE group by original_account_number;

Has anyone stumbled across any material on the web that may be able to help me with this task, or would anyone be able to possibly give me a hint??

Thanks in advance