I have a table XYZ with fields {Account, Serial No, Beneficiary Name, Status…….so on...}

None of the fields are unique.



Now I wish to generate unique serial numbers to each of the records such that I have:



Account Serial No

A1 1

A2 1

A1 2

A1 3

A3 1

A3 2

A3 3

A3 4



Hence it should dynamically increment serial No for each group of records (with same Account) and reset to 1 for a new group of account.

Once this is done I would be using {Account, Serial No} as the index.



I have the following procedure but it works as below:



Account Serial No

A1 3

A2 1

A1 3

A1 3

A3 4

A3 4

A3 4

A3 4









DECLARE



CURSOR XYZ_CURSOR IS

SELECT account FROM XYZ;



BEGIN --{

FOR XYZ_rec IN XYZ_CURSOR

LOOP --{

BEGIN --{

UPDATE XYZ a

SET a.serial_num = (select to_char(to_number(max(b.serial_num) + 1))

FROM XYZ b WHERE

b.acid = XYZ_rec.acid)

WHERE

a.acid = XYZ_rec.acid;



EXCEPTION

WHEN NO_DATA_FOUND THEN NULL;

END; --}

COMMIT;





END LOOP;--}

COMMIT;

DBMS_OUTPUT.PUT_LINE('ALL Rows Updated :' );



END; --}



Probably its because of the where clause in update ( WHERE a.acid = XYZ_rec.acid; ) which picks all the records with the same account from the table.





Is it possible to do this operation without a unique key to the table?





Please help.

Thanks