Search code examples
duplicatessql-updateoracle12c

Find duplicate records in a column and update the duplicates to max + 1 (oracle)


I want to find the duplicate values and update the duplicated records with max(col1)+1 leaving the first record as is

example

create table tab1 (col1 number(10), col2 varchar2(50),col3 date);
/
insert into tab1(col1,col2) values(101,'sts');
insert into tab1(col1,col2) values(101,'sts');
insert into tab1(col1,col2) values(102,'gghh');
insert into tab1(col1,col2) values(102,'hhh');
insert into tab1(col1,col2) values(103,'sss');
insert into tab1(col1,col2) values(103,'dss');
insert into tab1(col1,col2) values(103,'ddd');
insert into tab1(col1,col2) values(777,'Aliens');
/
Select * from tab1;
  • result
101 sts 
101 sts --duplicate
102 gghh    
102 hhh --duplicate
103 sss 
103 dss --duplicate
103 ddd --duplicate
777 Aliens
  • expected result after update (Which i am having difficulty achieving)
101 sts 
102 gghh    
103 sss 
777 Aliens
778 sts --altered from 101
779 hhh --altered from 102
780 dss --altered from 103
781 ddd --altered from 103

This is what i have got to till now

DECLARE
    v_cntr NUMBER := 0;
BEGIN 
FOR rec IN (
        SELECT rownumber,col1
    from (SELECT ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col1) rownumber, col1
    FROM tab1) X
    WHERE rownumber > 1
) 
LOOP
FOR rec2 IN (
    SELECT ROWID FROM tab1 WHERE col1 = rec.col1
    
)
loop
    v_cntr := v_cntr + 1;
    UPDATE tab1
    SET col1 = (Select max(col1)+1 from tab1)
    WHERE rowid = rec2.rowid;
    DBMS_OUTPUT.PUT_LINE ('done');
end LOOP;
END loop;
END;

Thanks All


Solution

  • I found the solution.

    DECLARE
        v_cntr NUMBER := 0;
    BEGIN 
    FOR rec IN (
        SELECT X.rownumber, X.rowid, X.col1 
    FROM (Select ROWID,col1, ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col1) as rownumber
          FROM tab1) X
    WHERE X.rownumber > 1
    )
    loop
        v_cntr := v_cntr + 1;
        UPDATE tab1
        SET col1 = (Select max(col1)+1 from tab1)
        WHERE rowid = rec.rowid;
        DBMS_OUTPUT.PUT_LINE ('done');
    END loop;
    --COMMIT;
    END;