Delete From One table based on another

I am trying to delete from one table all the rows that are in another table.

The two tables are tblUnmatched and tblmatched.  

I need to delete from Unmatched where the records exist in TblMatched.

DELETE tblUnmatched.*, Exists (Select * from tblMatches where tblUnmatched.Cusip = tblMatches.Cusip), tblUnmatched.[Fund #]=tblMatches].[Acct_No]));


The joins should be on tblUnmatched.Cusip = tblMatches.Cusip), tblUnmatched.[Fund #]=tblMatches].[Acct_No])

Thanks in advance!!!
leezacAsked:
Who is Participating?
 
tomcahillConnect With a Mentor Commented:
would this work:

delete from tblUnmatched where Cusip in (select Cusip from tblMatches)
0
 
leezacAuthor Commented:
It needs to be based on Cusip, Fund # and Shares
0
 
tomcahillCommented:
will there be rows with matching Cusip that don't have matching Fund# and shares?

You didn't mention shares in the OP.
0
 
leezacAuthor Commented:
will there be rows with matching Cusip that don't have matching Fund# and shares? - no

You didn't mention shares in the OP.  - yes - I forgot

Your code does work but what I need to do is find the macthing fund #, Cusip and shares as there may be the same cusip that I need to keep because the share did not match. I have a table of matched funds based on shares and need an unmatched table where the shares do not match.
0
 
leezacAuthor Commented:
This shows the joins but does says it cannot delete from specified tables.

DELETE tblUnmatched.*, tblUnmatched.Cusip
FROM tblUnmatched INNER JOIN tblMatches ON (tblUnmatched.[Fund #] = tblMatches.Acct_No) AND (tblUnmatched.F8 = tblMatches.Shares) AND (tblUnmatched.Cusip = tblMatches.Cusip)
WHERE (((tblUnmatched.Cusip) In (select Cusip from tblMatches)));
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.