none
Delete query not recognising specified table RRS feed

  • Question

  • I run a delete query, but it says "specify the table containing the records I want to delete" the table is specified and is identical to others but with different table names. 


    The Sql is:

    DELETE DISTINCTROW tblMembers.*, tblMembersNew.ID, *
    FROM tblMembers LEFT JOIN tblMembersNew ON tblMembers.ID = tblMembersNew.ID
    WHERE (((tblMembersNew.ID) Is Null));

    Any Ideas please?

    Monday, February 4, 2019 3:54 PM

Answers

  • I'd probably use a subquery, something along the lines of (untested aircode)

    DELETE 
    FROM tblMembers 
    WHERE ID In(
        SELECT DISTINCT tblMembers.ID
        FROM tblMembersNew RIGHT JOIN tblMembers ON tblMembersNew.ID = tblMembers.ID
        WHERE (tblMembersNew.ID Is Null)
    )
    That said, you should setup referential integrity in your table relationships to avoid the issue altogether.


    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Monday, February 4, 2019 4:15 PM

All replies

  • I'd probably use a subquery, something along the lines of (untested aircode)

    DELETE 
    FROM tblMembers 
    WHERE ID In(
        SELECT DISTINCT tblMembers.ID
        FROM tblMembersNew RIGHT JOIN tblMembers ON tblMembersNew.ID = tblMembers.ID
        WHERE (tblMembersNew.ID Is Null)
    )
    That said, you should setup referential integrity in your table relationships to avoid the issue altogether.


    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Monday, February 4, 2019 4:15 PM
  • Hi. Which table did you want to delete records from?
    Monday, February 4, 2019 4:18 PM
  • tblMembers
    Monday, February 4, 2019 4:34 PM
  • tblMembers

    Then, try Daniel's suggestion.
    Monday, February 4, 2019 5:05 PM
  • It worked, thank you very much. But I am not sure why yours did and mine did not? Is it because I have used the "*"? But once again thank you. 
    Monday, February 4, 2019 5:40 PM