Solved: Delete Duplicate Query

[Imdabaum]

As much as I enjoy a good wizard, I would like to get a little more familiar with writing the SQL on my own, not to mention that I can't seem to understand the instructions for the duplicate wizard. It never returns all the duplicates.. or the ones that I think would be duplicates.

I want have a process that creates records, but I want to clean it up afterwards by deleting the duplicate record.

The table has an ID, StructID, CRFID, VendorID, CustID, INVNbr, Amt, Date

The ID would be the only unique entry. So if the StructID, CRFID, VendorID, CustID, INVNbr, Amt, and Date are all the same, I just want the first record.

[Imdabaum]

Okay... just did some investigating and it appears that I want to do something you cannot do in SQL. Deleting duplicates deletes all duplicates leaving you with nothing.

I came up with this though:

DELETE Exists (SELECT REQUEST.ID, REQUEST.Recur_Invc_NBR, PLANS.Plan_Type, PLANS.RequiresInvoiceValidation
FROM TBL_CRF AS REQUEST INNER JOIN TBL_COMMISSION_PLAN_TYPE AS PLANS ON REQUEST.Commission_Plan_Type = PLANS.Commission_Plan_ID
WHERE (((PLANS.RequiresInvoiceValidation)=-1) AND (REQUEST.ID = COMMISSIONS.FK_CRFID) AND (QUEST.Recur_Invc_NBR <> Commissions.InvoiceNBR))) AS DeleteItem, COMMISSIONS.CommissionType, *
FROM TBL_COMMISSIONS_Earned AS COMMISSIONS
WHERE (((COMMISSIONS.CommissionType)="Recurring") AND ((Exists (SELECT REQUEST.ID, REQUEST.Recur_Invc_NBR, PLANS.Plan_Type, PLANS.RequiresInvoiceValidation
FROM TBL_CRF AS REQUEST INNER JOIN TBL_COMMISSION_PLAN_TYPE AS PLANS ON REQUEST.Commission_Plan_Type = PLANS.Commission_Plan_ID
WHERE REQUEST.ID = COMMISSIONS.FK_CRFID
AND REQUEST.Recur_Invc_NBR <> Commissions.InvoiceNBR))<>False));


It gets pretty ugly.
There are three tables that I'll need to pull from
REQUEST, COMMISSIONS, and PLANS

They just changed specs where there are no longer a specific range of plans that require this validation.
I got the value of Plans.RequiresInvoiceValidation into this exists statement but it doesn't seem to affect the Query.

It shows every record to be deleted that doesn't match regardless of whether the associated Request has a Commission_Plan_Type with RequiresInvoiceValidation checked or not.

In case anyone was looking at this. Sorry for the confusion.

[orange]

Consider a slightly revised table from your example

Table3 with fields
id,structId,CRFId,VendorId, CustId,INVNbr, Amount, MyDate

You said id will be unique -- so make it autoNumber

To populate your table, with duplicates run this query 5 or 6 times

INSERT INTO Table3 ( structId, CRFId, VendorId, CustId, INVNbr, Amt, MyDate )
VALUES (200, 300, 400, 500, 11200, 21.33, #3/21/2010#);

Now , as per your example, all fields, except field Id, have identical values

id StructID CRFID VendorID CustID INVNbr Amt myDate
9 200 300 400 500 11200 $21.33 3/21/2010
12 200 300 400 500 11200 $21.33 3/21/2010
13 200 300 400 500 11200 $21.33 3/21/2010
14 200 300 400 500 11200 $21.33 3/21/2010
15 200 300 400 500 11200 $21.33 3/21/2010
16 200 300 400 500 11200 $21.33 3/21/2010

To delete the duplicates run this query

delete K.*
FROM Table3 AS K
WHERE K.Id >(Select min(M.id) from Table3 as M);

id StructID CRFID VendorID CustID INVNbr Amt myDate
9 200 300 400 500 11200 $21.33 3/21/2010

Good luck.

[Movian]

Another Option that i use i find can be a little quicker, is to produce a new table from the sql statment using the DISTINCT command.

Then i delete the old table and rename the new to the old name. of course this depends on your sistuation

[orange]

Another Option that i use i find can be a little quicker, is to produce a new table from the sql statment using the DISTINCT command.

Then i delete the old table and rename the new to the old name. of course this depends on your sistuation

Yes, I use that also, and would normally do it that way. I just thought I'd try to solve his issue with query only.

[Imdabaum]

I ended up creating a sub query using a join on the REQUEST and the PLANS, then using the exists, I was able to delete all duplicates.

DELETE Exists (SELECT ID, Setup_Invc_NBR, RequiresInvoiceValidation
FROM zzQRY_Setup2010InvValidation AS Validator
WHERE Validator.ID = COMMISSIONS.FK_CRFID
AND Validator.Setup_Invc_NBR <> Commissions.InvoiceNBR) AS DeleteItem, COMMISSIONS.CommissionType, Exists (SELECT ID, Setup_Invc_NBR, RequiresInvoiceValidation FROM zzQRY_Setup2010InvValidation as Validator
WHERE Validator.ID = COMMISSIONS.FK_CRFID
AND Validator.Setup_Invc_NBR <> Commissions.InvoiceNBR) AS Expr1, COMMISSIONS.Date_Paid_In_Full, *
FROM TBL_COMMISSIONS_Earned AS COMMISSIONS
WHERE (((COMMISSIONS.CommissionType)="Setup") AND ((Exists (SELECT ID, Setup_Invc_NBR, RequiresInvoiceValidation FROM zzQRY_Setup2010InvValidation as Validator
WHERE Validator.ID = COMMISSIONS.FK_CRFID
AND Validator.Setup_Invc_NBR <> Commissions.InvoiceNBR))<>False) AND ((COMMISSIONS.Date_Paid_In_Full) Is Null));

This query ensures that the values are equal and if they aren't then it removes it as an invalid duplicate.