Delete query using to tables

Access
How would I write a query that would delete everything from table1 based on what is in table2?

Example: table1 has 10,000 records and table2 has 3,000 records that match the ones in table1 using a key field match.

so after running the delete query table1 should now have 7,000 records.
ssblueCoordinatorAsked:
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.

Ryan ChongSoftware Team LeadCommented:
try like:

delete from table1 where field1 in (select field2 from table2)
ssblueCoordinatorAuthor Commented:
DELETE MyTable.ID
FROM MyTable
WHERE (((MyTable.ID) In (select [MyTable.ID] from [MyTable2.ID])));


not working?????
irudykCommented:
Try

DELETE MyTable.*
FROM MyTable INNER JOIN MyTable2 ON MyTable.ID = MyTable2.ID
Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

Molnár IstvánHelpDesk / ProgrammerCommented:
you could use something like this (TEST IT before using in production enviroment !):

1.
WHILE (1=1)
BEGIN
     DELETE TOP(1000) FROM table1 WHERE field1 IN (SELECT field2 FROM table2) 
     IF @@ROWCOUNT < 1 BREAK
END

Open in new window


2.
WHILE (1=1)
BEGIN
     DELETE TOP(1000) FROM table1 INNERJOIN table2 ON table1.ID=table2.ID
     IF @@ROWCOUNT < 1 BREAK
END

Open in new window


instead of
WHILE (1=1)

Open in new window

you could use
WHILE @@ROWCOUNT <> 0

Open in new window


Inspired from SPEED UP HUGE DELETE
Mark EdwardsChief Technology OfficerCommented:
If anybody gets an Access DELETE query that has two tables joined to work, please let Microsoft and the rest of the world know, as they haven't been able to do it....

If this was SQL Server, however, it would work.

Also, this is Microsoft Access, not SQL Server - there is no @@RowCount in Access.....
ssblueCoordinatorAuthor Commented:
Thanks for that comment!!
Molnár IstvánHelpDesk / ProgrammerCommented:
Just an idea:

Maybe modifying the relationship between the parent and child table to "Cascade Delete Related Records"  and after that deleting just from parent table will delete all related child records too

When a record is deleted in a parent table, the child records are deleted also

Delete Table1.*
From Table1
Where Exists( Select 1 From Table2 Where Table2.ID= Table1.ID ) = True

Open in new window


Delete in MS Access when using JOIN's
Mark EdwardsChief Technology OfficerCommented:
Try the Access SQL query below.  Another person just had it work successfully for him.

DELETE *
 FROM MyTable
 WHERE MyTable.ID IN (SELECT [ID] FROM [MyTable2])

Open in new window

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
Fabrice LambertConsultingCommented:
Maybe modifying the relationship between the parent and child table to "Cascade Delete Related Records"  and after that deleting just from parent table will delete all related child records too
Bad idea IMO, a little mistake (from experience, these happen so fast) and nearly all data in the database are gone.
Ryan ChongSoftware Team LeadCommented:
Maybe modifying the relationship between the parent and child table to "Cascade Delete Related Records"  and after that deleting just from parent table will delete all related child records too
This is good approach to maintain data integrity but there's always a risk of data lost if parent record was deleted accidentally.
ssblueCoordinatorAuthor Commented:
Sorry Mark, that didn't work either, but thanks for the effort.
ssblueCoordinatorAuthor Commented:
Mark, this did work.  I had to change it to look for the TAG_Number instead of the ID.   Thanks!!!!


DELETE *
 FROM MyTable
 WHERE MyTable.TAG_Number IN (SELECT [TAG_Number] FROM [MyTable2])
ssblueCoordinatorAuthor Commented:
I want to thank everyone for all the insight, discussions and examples.  Sorry if my inability to explain things created some confusion but based on all the input I think everyone contributed to the final solutions.
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.