Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2013
    Posts
    53

    Unanswered: Delete common records found in table 1 from table 2

    Hey All, in need of some assistance Can seem to make my delete query work with the following synatx:

    DELETE CCODE_20130426.*, Master_HMA.*
    FROM CCODE_20130426 INNER JOIN Master_HMA ON CCODE_20130426.[Part Number (valid)] = Master_HMA.[Part Number (valid)]

    Basically trying to only delete records from Master_HMA which are common in both tables. Thanks in advance!

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Off the top of my head:

    Code:
    DELETE FROM Master_HMA
    WHERE EXISTS 
       (SELECT *
          FROM CCODE_20130426
         WHERE CCODE_20130426.PartNumber  = Master_HMA.PartNumber
       )

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    WHERE EXISTS is not part of MS Access SQL and will not work (it only works in MS SQL Server). Try:
    Code:
    DELETE * FROM Master_HMA
    WHERE Master_HMA.[Part Number (valid)] IN 
        ( SELECT CCODE_20130426.[Part Number (valid)]
          FROM CCODE_20130426 LEFT JOIN Master_HMA
          ON CCODE_20130426.[Part Number (valid)] = Master_HMA.[Part Number (valid)]
          WHERE CCODE_20130426.[Part Number (valid)] Is Not Null
        );
    Have a nice day!

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by Sinndho View Post
    WHERE EXISTS is not part of MS Access SQL and will not work (it only works in MS SQL Server).
    According to my 2 minutes of googling this, MS Access does support EXISTS:

    List of Microsoft Jet 4.0 reserved words
    The other links to older versions on this page also show support for EXISTS.

    Access Subquery Techniques
    This shows an example of using WHERE EXISTS.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Although I use the EXISTS predicate quite often when working with MS SQL Server, I never knew it could be used within an Access query. I learned something today. Thanks!
    Have a nice day!

  6. #6
    Join Date
    Jan 2013
    Posts
    53
    Thanks you both for all your help. Have a great weekend!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •