[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

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!!!
0
leezac
Asked:
leezac
  • 3
  • 2
1 Solution
 
tomcahillCommented:
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now