Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    5

    Unanswered: Deleting from more than one table

    I have two tables that are related:

    table1
    ------
    t1ID
    t1Name

    table2
    ------
    t2ID
    t1ID
    t2Name

    I can delete the related when there are records in both tables with:

    DELETE table1, table2 FROM table1, table2 WHERE table1.t1ID = 1 AND table1.t1ID = table2.t1ID;

    However the problem when I use the above query and there are no records relating to table1 in table2 as it doesn't delete the record in table1.

    I was advised to use a left join:

    DELETE table1, table2 FROM table1 LEFT JOIN table2 ON table1.t1ID = 1 AND table1.t1ID = table2.t1ID;

    But this deletes all the records in Table1!

    Any suggestions will be much appreciated!

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DELETE table1, table2
       FROM table1
       LEFT JOIN table2 ON table1.t1ID = table2.t1ID
       WHERE table1.t1ID = 1;
    The ON clause should only be used to describe how rows match when joined. The WHERE clause should be used to "filter" the results to affect only the rows you want.

    -PatP

Posting Permissions

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