Delet query using another table

[Guest]

I do a make-table query and then try to use it to delete records from another
table called RC_Main, The SQL stmnt looks like:

DELETE RC_Main.*
FROM [Pending Deletion IDs] INNER JOIN RC_Main ON [Pending Deletion
IDs].ID=RC_Main.ID;

But I get a syntax error on the [Pending Deletion IDs].id. What is wrong?

Mike J

 

[John Spencer]

Guessing, but I would check to make sure that
[Pending Deletion IDs].ID and RC_Main.ID
are both the same type of field.

 

[Guest]

The statement is trying to delete records in the RC_Main table from Pending
Deletion IDs table. This isn't going to happen. ;-)

I prefer a subquery for this kind of deletion:

DELETE RC_Main.*
FROM RC_Main
WHERE RC_Main.ID In
(select ID from [Pending Deletion IDs]);

 

[Guest]

OK, thanks that worked. Another ? is can this query be used to delete rcords
from several tables? According to help, it sounds like you can but when I
tried it said 'cannot delete from specified tables.

Mike J

The statement is trying to delete records in the RC_Main table from Pending
Deletion IDs table. This isn't going to happen. ;-)

I prefer a subquery for this kind of deletion:

DELETE RC_Main.*
FROM RC_Main
WHERE RC_Main.ID In
(select ID from [Pending Deletion IDs]);

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I do a make-table query and then try to use it to delete records from another
table called RC_Main, The SQL stmnt looks like:

DELETE RC_Main.*
FROM [Pending Deletion IDs] INNER JOIN RC_Main ON [Pending Deletion
IDs].ID=RC_Main.ID;

But I get a syntax error on the [Pending Deletion IDs].id. What is wrong?

Mike J

 

[Guest]

With a lot of coding, it could be done; however, as a straight forward SQL
query, no. You probably need to create a specific query for each table where
you want to delete records. However after creating all the queries, there's
nothing stopping you from running them all at once buy putting them in a
macro or module. Just remember to Set Warnings off at the start and Set
Warnings back on at the end.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

OK, thanks that worked. Another ? is can this query be used to delete rcords
from several tables? According to help, it sounds like you can but when I
tried it said 'cannot delete from specified tables.

Mike J

The statement is trying to delete records in the RC_Main table from Pending
Deletion IDs table. This isn't going to happen. ;-)

I prefer a subquery for this kind of deletion:

DELETE RC_Main.*
FROM RC_Main
WHERE RC_Main.ID In
(select ID from [Pending Deletion IDs]);

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I do a make-table query and then try to use it to delete records from another
table called RC_Main, The SQL stmnt looks like:

DELETE RC_Main.*
FROM [Pending Deletion IDs] INNER JOIN RC_Main ON [Pending Deletion
IDs].ID=RC_Main.ID;

But I get a syntax error on the [Pending Deletion IDs].id. What is wrong?

Mike J