Delete Query Using values in another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Trying to run a delete query off table 'A' keyed on a number id. Have a second table with a list of number id's that need to deleted. I can successfully delete if the actual numbers are identified within the query but I get an error message if I try to referrence another table that has a list of numbers. - - any ideas?
 
Trying to run a delete query off table 'A' keyed on a number id. Have a second table with a list of number id's that need to deleted. I can successfully delete if the actual numbers are identified within the query but I get an error message if I try to referrence another table that has a list of numbers. - - any ideas?
Click to expand...

What error message? How are you referencing the other table? Please
post the SQL.
 
-----Original Message-----
Click to expand...
number id. Have a second table with a list of number id's
that need to deleted. I can successfully delete if the
actual numbers are identified within the query but I get
an error message if I try to referrence another table that
has a list of numbers. - - any ideas?
What error message? How are you referencing the other table? Please
post the SQL.


.
Click to expand...
Error: "Could not delete from specified tables"

DELETE [Table_A].*, [Table_A].[number]
FROM [Table_A], [table_b]
WHERE ((([Table_A].number)=[table_b].[number]));

Trying to delete records from Table_A where the number
from table_b matches the number from Table_A.

Thanks.
 
Trying to delete records from Table_A where the number
from table_b matches the number from Table_A.
Click to expand...

Access uses the SQL-92 JOIN clause rather than the older join
implemented in the WHERE clause: Try

DELETE [Table_A].*
FROM [Table_A] INNER JOIN [table_b]
ON [Table_A].number)=[table_b].[number];

If [number] is the Primary Key of Table_A it should work.

If it isn't the key, you can use a Subquery:

DELETE [Table_A].*
FROM [Table_A]
WHERE [number] IN (SELECT [number] FROM Table_B);
 
Back
Top