Can't get delete query to work...

[Matt P]

I am not sure exactly what I need to do to get this delete query to
work. I want to delete the records from tblCities that don't match
tblCompanyAddresses. The error is "Specify the table containing the
records you want to delete."

DELETE tblCities.ID, tblCities.City, tblCities.State,
tblCities.ShowCity
FROM tblCities LEFT JOIN tblCompanyAddresses ON (tblCities.[City] =
tblCompanyAddresses.[City]) AND (tblCities.State =
tblCompanyAddresses.State)
WHERE (((tblCompanyAddresses.City) Is Null));

Thanks for any suggestions!
-Matt P

 

[Allen Browne]

Try a subquery:
http://allenbrowne.com/subquery-01.html#DeleteUnmatched

 

[Matt P]

Try a subquery:
   http://allenbrowne.com/subquery-01.html#DeleteUnmatched

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I am not sure exactly what I need to do to get this delete query to
work.  I want to delete the records from tblCities that don't match
tblCompanyAddresses. The error is "Specify the table containing the
records you want to delete."

DELETE tblCities.ID, tblCities.City, tblCities.State,
tblCities.ShowCity
FROM tblCities LEFT JOIN tblCompanyAddresses ON (tblCities.[City] =
tblCompanyAddresses.[City]) AND (tblCities.State =
tblCompanyAddresses.State)
WHERE (((tblCompanyAddresses.City) Is Null));

Thanks for any suggestions!
-Matt P

Thanks worked great, I just followed your example and here is my sql:
DELETE *
FROM tblCities
WHERE NOT EXISTS
(SELECT City, State
FROM tblCompanyAddresses
WHERE tblCompanyAddresses.City = tblCities.City AND
tblCompanyAddresses.State = tblCities.State);

Would be even better if you could preview the data being deleted, but
at least it is funcional..
Thanks for introducing me to subqueries!

-Matt P