Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem deleting duplicate records.

Posted on 2015-02-19
4
Medium Priority
?
54 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 48

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

722 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