Home » SQL & PL/SQL » SQL & PL/SQL » Combine Duplicates
Combine Duplicates [message #18684] Mon, 11 February 2002 02:20 Go to next message
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 Go to previous messageGo to next message
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
Re: Combine Duplicates [message #18690 is a reply to message #18684] Mon, 11 February 2002 04:31 Go to previous message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
did it solve your problem ?.let me know.
Previous Topic: sp1.msb
Next Topic: Re: triggers
Goto Forum:
  


Current Time: Thu Mar 28 20:37:39 CDT 2024