Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete SQL (Access 2002)

    Hi

    A quick search of the forum hasn't found the answer to this small problem.

    I'm trying to write a delete sql which deletes records from two tables based on criteria from one of the tables. The table join is one to many relationship (see attahed). The following code is my initial attempt, which failed (error message said could not delete from table):

    DELETE DISTINCTROW Active.*, NEDI.*
    FROM Active INNER JOIN NEDI ON Active.Active_ID = NEDI.Active_ID
    WHERE (((Active.ADI_WHO)=0) AND ((Active.ADI_ERMA)=0));

    I know there are some 'tricks' to doing this, but I can't recall what they are!

    If further info required, please advise.

    Thanks and Regards
    WTH
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete SQL (Access 2002)

    Your query is trying to delete records from two tables at once. That is not possible. You must decide from which table you want to delete records.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete SQL (Access 2002)

    Hi Hans

    Thanks for the speedy reply. Can you suggest an alternative way to avoid a manual delete from the second table (NEDI) Obviously, deleting records from the first table (actives) is not going to be a problem, but this will leave orphan records in the table NEDI. The issue I'm trying to resolve is to delete the linked records in NEDI without doing this manually.

    Thanks & Regards

    WTH

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete SQL (Access 2002)

    According to your screenshot, turning on "Enforce Referential Integrity" and "Cascade Delete Related Records" for the relationship between Active and NEDI is not an option. Try this SQL:

    DELETE NEDI.* FROM NEDI WHERE NEDI.NediID In (SELECT NEDI.NediID FROM NEDI INNER JOIN Active ON NEDI.ActiveID = Active.ActiveID WHERE Active.ADI_WHO=0 AND Active.ADI_ERMA=0)

    This "trick" of using a subquery is useful when deleting records involving a join.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete SQL (Access 2002)

    Hi Hans

    It works perfectly, many thanks for the advice.

    Regards
    WTH

Posting Permissions

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