Delete Duplicate Records In Second Table

I've got two unrelated tables (table1 & table2), each with a text field called [DATA1].

I want to delete records in table2 only, if there is a duplicate value in [DATA1]
(So there will be no duplicates).

So I created a query with a [DATA1] <==> [DATA1] relationship, and it shows up any duplicates. However, deleting records within this query, deletes duplicate records in both tables, which is not what I want.

I could do it in a roundabout way by manually marking the records for deletion, within the above query (in another field) and then running a delete query.

I will need to delete duplicates regularly, so I need an efficient one click solution (probably using RunSQL a few times from a command button).

Any ideas?
LVL 24
EirmanChief Operations ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
do you have a unique id field in table2?

try this query

delete *
from table2
where ID Not In(select max(t2.ID), t2.[data1] from table2 as t2 group by t2.[data1])
0
chaauCommented:
I think the query as simple as this will do:
DELETE FROM Table2 WHERE Data1 IN (SELECT Data1 FROM Table1)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EirmanChief Operations ManagerAuthor Commented:
do you have a unique id field in table2?
DATA1 (indexed - no duplicates allowed) is unique in both tables ... there are no other unique fields in either tables.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Rey Obrero (Capricorn1)Commented:
<I want to delete records in table2 only, if there is a duplicate value in [DATA1]>

do you mean duplicate value in table1?
0
EirmanChief Operations ManagerAuthor Commented:
do you mean duplicate value in table1?
If there is a record in table1 with "ABCD" in DATA1      and
there is a record in table2 with "ABCD" in DATA1

I want to delete the 'duplicate' record in table 2
0
chaauCommented:
What's wrong with the query I have provided then?
DELETE FROM Table2 WHERE Data1 IN (SELECT Data1 FROM Table1)

Open in new window

0
EirmanChief Operations ManagerAuthor Commented:
What's wrong with the query I have provided then?
I didn't say anything was wrong with any suggestions ... and I certainly wouldn't before testing. I need to create a few test tables to try out the various suggestions.

and it's 2:30 am here in Ireland and I'm off to bed so it will be 15 hours or so before my next post ... good night to you all.
0
EirmanChief Operations ManagerAuthor Commented:
That worked very nicely chaau ... many thanks

Thanks for the help capricorn1 .... In this case, the simplest solution was the best.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.