asked on

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!!!

ASKER CERTIFIED SOLUTION

tomcahill

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

leezac

ASKER

It needs to be based on Cusip, Fund # and Shares

tomcahill

will there be rows with matching Cusip that don't have matching Fund# and shares?

You didn't mention shares in the OP.

leezac

ASKER

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.

leezac

ASKER

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)));