Can't get delete query to work...

  • Thread starter Thread starter Matt P
  • Start date Start date
M

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
 
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."
Click to expand...
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));
Click to expand...
Thanks for any suggestions!
-Matt P
Click to expand...
Click to expand...

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
 
Back
Top