removing dups based on info from another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

removing dups based on info from another table

Author

Topic

renecarol
Starting Member

5 Posts

Posted - 2011-08-04 : 14:02:15

I am working on some code to delete duplicate records where someone has moved from one insurance plan to another it will show the most recent plan (have to be open ended, would have an end date of <>"00000000") , I can't just delete records of everyone that does not have an end date of <>"00000000"; it has to be the records that are duplicate member numbers. If someone was covered through the end of the month ( i.e. they left the company) they need to be accounted for. I created a new table to populate with duplicates called "CelMemId". I ran this code:
DELETE FROM CovElig b
WHERE b.MemberId in
(SELECT distinct CelMemId.MemId
FROM CelMemId, CovElig a
WHERE CelMemId.MemId = a.MemberId
AND a.EndDt_mem <>"00000000");

And this delete query has been running since yesterday afternoon (about 24 hours) without finding any records. I believe that this could be an Access issue... that Access just cannot handle all the data that is in our database. We are currently programming our database in Access but will be moving the SQL code over into an Oracle Database in the Fall. Thanks for any help with this issue. I've posted this question on a couple of different sites but haven't got any feedback yet.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-08-05 : 05:04:01

1. you will get better replies if you give us sample input data and sample expected results.
2. you haven't said how many rows involived in the input table, nor the expected deletion rate.
3. what's your hardware like?
4. you should consider batching your delete into smaller batches. one large delete may be too hard to handle in a managable time.

renecarol
Starting Member

5 Posts

Posted - 2011-08-05 : 09:21:26

I got this reply from another discussion forum that I posted on. With this code I could delete the duplicates without having to create another table to feed the duplicates into (I don't know why I didn't think of that).

To see the records it would delete:

SELECT MemberID, EndDt_Mem
FROM CovElig
WHERE MemberID in (SELECT MemberID
FROM CovElig
GROUP BY MemberID
HAVING COUNT(MemberID)>1)
AND EndDt_Mem <> "00000000";

To delete the records:
DELETE *
FROM CovElig
WHERE MemberID in
(SELECT MemberID
FROM CovElig
GROUP BY MemberID
HAVING COUNT(MemberID)>1)
AND EndDt_Mem <> "00000000";

The problem with that is it is deleting 22 records. I have a problem with 5 of the deletes. The 5 that are problems are all records that have a count of more than 1 but do not have an end date of “00000000”. They apparently end at the end of the month but have a change during the month. For example the person started a plan Jan 1 end Jun 1, then started another plan Jun 2 (say they had a baby on Jun 2nd added her so they started a family plan and ended a employee+spouse plan) and then left the company at the end of the month Jun 30th so now they have an end date. I’m not sure how to account for those people. I think I would’ve still had this problem if I had been able to get the original code that I was trying to write to work.
I am looking to delete 15 records of about 40,000. I'm not sure what the server the company I work for looks like or how big it is. Aside from that I know we are getting a bigger computer for the Oracle migration because what we have isn't enough to handle it.

dolot
Starting Member

8 Posts

Posted - 2011-08-16 : 12:16:03

One thing you could try to speed up the original delete query would be to add an additional join to the subselect:

DELETE FROM CovElig b
WHERE b.MemberId in
(SELECT distinct CelMemId.MemId
FROM CelMemId, CovElig a
WHERE CelMemId.MemId = a.MemberId
AND a.EndDt_mem <>"00000000"
and a.MemberID = b.MemberID);

This is assuming you have the appropriate indexes on the tables in question.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources