Delete query using to tables

ssblue
ssblue used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
try like:

delete from table1 where field1 in (select field2 from table2)
ssblueCoordinator

Author

Commented:
DELETE MyTable.ID
FROM MyTable
WHERE (((MyTable.ID) In (select [MyTable.ID] from [MyTable2.ID])));


not working?????
Top Expert 2008

Commented:
Try

DELETE MyTable.*
FROM MyTable INNER JOIN MyTable2 ON MyTable.ID = MyTable2.ID
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Molnár IstvánHelpDesk / Programmer

Commented:
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 Officer

Commented:
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.....
ssblueCoordinator

Author

Commented:
Thanks for that comment!!
Molnár IstvánHelpDesk / Programmer

Commented:
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
Chief Technology Officer
Commented:
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

Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
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 Lead

Commented:
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.
ssblueCoordinator

Author

Commented:
Sorry Mark, that didn't work either, but thanks for the effort.
ssblueCoordinator

Author

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])
ssblueCoordinator

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial