update duplicate records

[latha9]

Hi all,

I have to find the people who have same last name,birth_date and org_id with different peo_id.After finding,i have to update the other tables.if the last name has more than 1 peo_id then i have to take minimum peo_id and update the other peo_id's to that min peo_id.i have written a procedure.but it's not giving the correct result.Can any one please help.I need it very urgent.

CREATE OR REPLACE PROCEDURE Merge_delete_Duplicate_Clients AS

pv_good_peo_id NUMBER;
--pv_bad_peo_id NUMBER ;

CURSOR peo_merge is

SELECT psr.peo_id, psr.FIRST_NAME, psr.LAST_NAME,psr.BIRTH_DATE, psr.OWNER_ORG_ID
FROM PROV_SERVICE_RECIPIENT@eispenet psr
WHERE psr.FIRST_NAME||psr.LAST_NAME||psr.BIRTH_DATE||psr.OWNER_ORG_ID IN (
SELECT psr.FIRST_NAME||psr.LAST_NAME||psr.BIRTH_DATE||psr.OWNER_ORG_ID
FROM PROV_SERVICE_RECIPIENT@eispenet psr
GROUP BY psr.FIRST_NAME, psr.LAST_NAME,psr.BIRTH_DATE, psr.OWNER_ORG_ID
HAVING COUNT(*) > 1)
ORDER BY psr.OWNER_ORG_ID,psr.LAST_NAME,psr.FIRST_NAME;

BEGIN

for i in peo_merge loop

select min(peo_id)
into pv_good_peo_id
from PROV_SERVICE_RECIPIENT@eispenet psr
where last_name = i.last_name
and birth_date = i.birth_date
and owner_org_id = i.owner_org_id;

if i.peo_id != pv_good_peo_id then


UPDATE eisprov.ADDRESS SET PEO_ID = pv_good_peo_id WHERE peo_id = i.peo_id;

UPDATE eisprov.ADDRESS SET created_by = pv_good_peo_id WHERE created_by = i.peo_id;

UPDATE eisprov.ADDRESS SET modified_by = pv_good_peo_id WHERE modified_by = i.peo_id;



UPDATE eisprov.DEPT_ASSESSMENT SET PEO_ID = pv_good_peo_id WHERE peo_id = i.peo_id;

UPDATE eisprov.DEPT_ASSESSMENT SET created_by = pv_good_peo_id WHERE created_by = i.peo_id;

UPDATE eisprov.DEPT_ASSESSMENT SET modified_by = pv_good_peo_id WHERE modified_by = i.peo_id;



UPDATE eisprov.PEOPLE_OTHER_ID SET PEO_ID = pv_good_peo_id WHERE peo_id = i.peo_id;

UPDATE eisprov.PEOPLE_OTHER_ID SET created_by = pv_good_peo_id WHERE created_by = i.peo_id;

UPDATE eisprov.PEOPLE_OTHER_ID SET modified_by = pv_good_peo_id WHERE modified_by = i.peo_id;



UPDATE eisprov.PEOPLE_RELATE SET PEO_ID = pv_good_peo_id WHERE peo_id = i.peo_id;

UPDATE eisprov.PEOPLE_RELATE SET RELATED_PEO_ID = pv_good_peo_id WHERE RELATED_PEO_ID = i.peo_id;

UPDATE eisprov.PEOPLE_RELATE SET created_by = pv_good_peo_id WHERE created_by = i.peo_id;

UPDATE eisprov.PEOPLE_RELATE SET modified_by = pv_good_peo_id WHERE modified_by = i.peo_id;



UPDATE eisprov.MHENROLLMENT_FORM_BATCH_NEW_V2 SET EIS_PEO_ID = pv_good_peo_id WHERE EIS_peo_id = i.peo_id;


UPDATE eisprov.MH_ISP_BATCH_FEED SET EIS_PEO_ID = pv_good_peo_id WHERE EIS_peo_id = i.peo_id;


DELETE FROM eisprov.PROV_SERVICE_RECIPIENT WHERE PEO_ID = i.peo_id;

end if;

end loop;

COMMIT;


END;

/

Thanks,
Latha

 

[Wholsea]

how about...


--First, lets find the people who have more than one record...


CURSOR find_multiple_users
IS
SELECT COUNT (*) cnt, last_name, birth_date, org_id
FROM people_table
GROUP BY last_name, birth_date, org_id
HAVING COUNT (*) > 1;

CURSOR users_to_change (
p_last_name NUMBER,
p_birth_date VARCHAR,
p_org_id NUMBER
)
IS
SELECT peo_id
FROM people_table
WHERE last_name = p_last_name
AND birth_date = p_birth_date
AND org_id = p_org_id;
--Now you will loop through that data set to find the min(peo_id) for that user...
BEGIN
FOR rec_users IN find_multiple_users
LOOP
-- Find the ID you are changing to...
SELECT MIN (peo_id)
INTO l_peo_id
FROM people_table
WHERE last_name = rec_users.last_name
AND birth_date = rec_users.birth_date
AND org_id = rec_users.org_id;

-- Now that we know what are are changing the IDs to
-- we need to loop through the users who have this same
-- information and update the tokens in the other tables...
FOR rec_change IN users_to_change (rec_users.last_name,
rec_users.birth_date,
rec_users.org_id
)
LOOP
UPDATE some_tabletable
SET peo_id = l_peo_id
WHERE peo_id = rec_change.peo_id;

COMMIT;
END LOOP; -- End of update loop
END LOOP;

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[taupirho]

This will give you all the min pid's and their associated last_names for all last_name/birth_date/org_id that have the same pid

select distinct p_id,last_name
from(
select count(*)
over (partition by last_name, birth_date, org_id,)cnt,
min(peo_id) over (partition by last_name) p_id,
id,name
from table1)
where cnt > 1

So you just need to cursor around these records and do an
update

e.g

for c1_rec in (select distinct p_id,last_name
from(
select count(*)
over (partition by last_name, birth_date, org_id,)cnt,
min(peo_id) over (partition by last_name) p_id,
id,name
from table1)
where cnt > 1
)
Loop
update table2 set id = c1_rec.pid
where name = c1_rec.name;
End loop;