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;
101 sts
101 sts --duplicate
102 gghh
102 hhh --duplicate
103 sss
103 dss --duplicate
103 ddd --duplicate
777 Aliens
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
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;