asked on

Problem deleting duplicate records.

I have a table of parts reservations that includes unique Reservation and (many to one) parts numbers. There cannot be duplicate part numbers for any Reservation. I've isolated the duplicates by creating a query of distinct reservation and parts records by using Max of the IDs for the distinct and selecting the IDs in the table Not Like the Max of IDs. The delete query says I need to specify a table to delete from, but I don't get it. The SQL looks like this:

DELETE PickListT.PID
FROM DuplicatePartsRsrvtnsQ INNER JOIN PickListT ON DuplicatePartsRsrvtnsQ.PID = PickListT.PID;

Open in new window

But I get this:

What am I missing?

Dale Fye

Try using a NOT IN ( ) clause rather than a join

DELETE FROM PickListT
WHERE [ID] NOT IN (SELECT PID FROM DuplicatePartsRsrvtnsQ)

NOTE: Make a backup copy of your database before you try this!!!!!

Omer-Pitou

Use this syntax:
Delete <alias to delete> From table1 alias1 Inner Join table2 alias1 ON alias1.keyField=alias2.keyField.
This syntax worked in MySQL and SQL Server, and should work in your case.
In brief, just after DELETE you should only put the name of the alias you want to delete.
Your correct statement in your case is
DELETE t2
FROM DuplicatePartsRsrvtnsQ t1 INNER JOIN PickListT t2 ON t1.PID = t2.PID;
Note: I just corrected your statement, without questioning what you're trying to achieve.

ASKER CERTIFIED SOLUTION

Jay Williams

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jay Williams

ASKER

I realized that deleting the records before reviewing them may not always be the best solution, so I chose the stated approach.