Delete From One table based on another

Posted on 2012-09-18
Last Modified: 2012-09-19
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!!!
Question by:leezac
    LVL 9

    Accepted Solution

    would this work:

    delete from tblUnmatched where Cusip in (select Cusip from tblMatches)

    Author Comment

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

    Expert Comment

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

    You didn't mention shares in the OP.

    Author Comment

    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.

    Author Comment

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

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now