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:
When I try to run the delete query, I get this.What am I missing?
Jay WilliamsOwnerAsked:
Who is Participating?
 
Jay WilliamsConnect With a Mentor OwnerAuthor Commented:
Thanks for the input.  I realized there is a preferable strategy, and that is to update the records (for review) and then query and delete the updated records post review.
0
 
Dale FyeCommented:
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!!!!!
0
 
Omer-PitouCommented:
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.
0
 
Jay WilliamsOwnerAuthor Commented:
I realized that deleting the records before reviewing them may not always be the best solution, so I chose the stated approach.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.