SQL Question

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

Guest

Hello,
I have 2 tables and the key fields in both tables are CoName and
ContactName. I am comparing the 2 tables in order to delete matching records
in the other table. Here is my SQL string:

DELETE Contacts.* FROM Contacts WHERE Contacts.CoName In
(Select Contacts.CoName FRom Contacts INNER JOIN DeleteContacts ON
Contacts.CoName=DeleteContacts.CoName) And (Contacts.Contact=DeleteContact));

It deletes all Contacts that have that CoName, but I want it to delete only
the Contacts where the CoName and the ContactName match. I tried this, but
it gives me an error:

DELETE Contacts.* FROM Contacts WHERE Contacts.CoName and Contacts.Contact In
(Select Contacts.CoName FRom Contacts INNER JOIN DeleteContacts ON
Contacts.CoName=DeleteContacts.CoName) And (Contacts.Contact=DeleteContact));

I know it's probably syntax, but need help.

Thanks so much! D.
 
If the CoName and ContactName combinations are unique keys in both tables,
you may use the following:
DELETE tblContacts.*
FROM tblContacts INNER JOIN tblContactsDelete ON (tblContacts.ContactName =
tblContactsDelete.ContactName) AND (tblContacts.CoName =
tblContactsDelete.CoName);
 
Back
Top