Delete query using to tables

try like:

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

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


not working?????

Try

DELETE MyTable.*
FROM MyTable INNER JOIN MyTable2 ON MyTable.ID = MyTable2.ID

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

Select allOpen 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

Select allOpen in new window

instead of

WHILE (1=1)

Select allOpen in new window

you could use

WHILE @@ROWCOUNT <> 0

Select allOpen in new window

Inspired from SPEED UP HUGE DELETE

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.....

Thanks for that comment!!

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

Select allOpen in new window

Delete in MS Access when using JOIN's

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.

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.

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

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

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.