Cannot delete from 1 side if no mathing on many side

  • Thread starter Thread starter Song
  • Start date Start date
S

Song

I try to delete agent (1 side) that has no matching record in master (many
side) but it would not allow me to do so. I can select, but not delete. Here
is my sql

DELETE tblAgent.*
FROM tblAgent LEFT JOIN tblMaster ON tblAgent.AgentID = tblMaster.AgentID
WHERE (((tblMaster.AgentID) Is Null));
 
This is untested, so make a copy, but I think this might do it:

DELETE * FROM tblAgent WHERE AgentID NOT IN(SELECT AgentID FROM tblMaster
WHERE AgentID IS NOT NULL);
 
Back
Top