Combine Duplicates [message #18684] |
Mon, 11 February 2002 02:20 |
Rich Petersen
Messages: 22 Registered: February 2002
|
Junior Member |
|
|
Hello all,
I have a table called MAINFOLDER, with, among other things, has a NAME and a FOLDER_ID columns. This FOLDER_ID is a foreign key in the BINDER table.
The customer has run a load multiple times, and now they have duplicates within the MAINFOLDER table. SO lets say we have a NAME/FOLDER_ID as follows:
NAME1 5
NAME1 6
And in the BINDER table, we have the same column, FOLDER_ID.
I have to delete one of the folders:
Delete from MAINFOLDER where FOLDER_ID=6
then I have to go to the BINDER table and update it, and set the deleted FOLDER_ID to the one that still exists:
UPDATE BINDER SET FOLDER_ID=5 where FOLDER_ID=6.
However, I have to do this numerous times, since they have many duplicate folders. Also, some folders have more than one duplicate.
Anyway to do this with a script?
|
|
|
Re: Combine Duplicates [message #18688 is a reply to message #18684] |
Mon, 11 February 2002 04:14 |
pratap kumar tripathy
Messages: 660 Registered: January 2002
|
Senior Member |
|
|
hi,
here is the script to do that. take the backup, before testing the script. there in no commit in script , so once u r happy with the script then u can commit the data
declare
cursor all_name_cur is
select name
from mainfolder
group by name having count(*)>1;
cursor upd_cur(v_name varchar2) is
select name,folder_id
from mainfolder
where name=v_name;
v_min number;
begin
for anc in all_name_cur --select duplicate name
loop
select min(folder_id) into v_min from mainfolder where name=anc.name; --find the minimum
for uc in upd_cur(anc.name)
loop
update binder set folder_id=v_min where folder_id=uc.folder_id;
end loop;
for uc in upd_cur(anc.name)
loop
if uc.folder_id != v_min then
delete from mainfolder where folder_id=uc.folder_id;
end if;
end loop;
end loop;
end;
/
cheeres
pratap
|
|
|
|