Joining tables in DELETE statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Joining tables in DELETE statement

  1. #1
    Join Date
    Jun 2005
    Posts
    11

    Joining tables in DELETE statement

    Can anyone please tell me how to join a table and a view to delete matching rows from the table?

    Here's a sample of my problem. I have a SQL statement in the following fashion:

    SELECT t1.*
    FROM MyTable1 t1,
    (
    SELECT t2.ID
    FROM MyTable2 t2, MyTable3 t3
    WHERE t2.ID = t3.ID
    ) v1
    WHERE t1.ID = v1.ID;

    Now, I want to delete all the rows that have been selected. I know this works only in SQL Server but not ORACLE, but I am trying to give an idea of what I am trying to accomplish...

    DELETE FROM t1
    FROM MyTable1 t1,
    (
    SELECT t2.ID
    FROM MyTable2 t2, MyTable3 t3
    WHERE t2.ID = t3.ID
    ) v1
    WHERE t1.ID = v1.ID;

    Can anyone please tell me how to join a table and a view to delete matching rows from the table?

    Your help is grately appreciated.

    Regards,
    Desi Rookie

  2. #2
    Join Date
    Jun 2005
    Location
    Calgary, Alberta, Canada
    Posts
    9
    How about something like

    DELETE FROM MyTable WHERE ID in (SELECT t2.ID FROM MyTable2 t2, MyTable3 t3 WHERE t2.ID = t3.ID);

    ?

    Anyway, check the SQL Reference manual, there are lots of examples of DELETEs.

Posting Permissions

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


Click Here to Expand Forum to Full Width