Oracle - Clean Up duplicate keys

Case:
Assume that you have  a table with duplicate ids , now you want to fix duplicate ids and want to make it all id values unique

Solution:

Step1: First  collect a dataset for duplicate IDs

CREATE TABLE  TMP AS

SELECT
ROWID  DEST_ROWID ,
ID PREV_ID ,
( SELECT  MAX( ID ) FROM TABLE_TEST  S  ) + ROWNUM  NEW_ID
FROM TABLE_TEST
WHERE
ID IN
(
     SELECT DISTINCT ID
     FROM TABLE_TEST
     GROUP BY ID
     HAVING COUNT(1)>1
)

Step2: Now we have a table that contains DUPLICATE_IDs and ROWID value of our table ( which is useful for updates )


Step3: Assing PK to  DEST_ROWID column on TMP table ( need for update )



Final Step:



UPDATE
(
SELECT  A.ID TARGET_ID , B.NEW_ID UPD_ID
FROM
TABLE_TEST  A , TMP B
WHERE
A.ROWID= B.DEST_ROWID
)
SET TARGET_ID = UPD_ID;
COMMIT;