Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Problem deleting duplicate records.

Posted on 2015-02-19
4
Medium Priority
?
55 Views
Last Modified: 2015-03-03
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?
0
Comment
Question by:Jay Williams
  • 2
4 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40618825
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
 

Expert Comment

by:Omer-Pitou
ID: 40618890
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
 

Accepted Solution

by:
Jay Williams earned 0 total points
ID: 40625619
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
 

Author Closing Comment

by:Jay Williams
ID: 40641476
I realized that deleting the records before reviewing them may not always be the best solution, so I chose the stated approach.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question