Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    12

    Question Unanswered: Delete From Table With Joins

    Hi,

    Could anyone please tell me the equivalent DML for the following T-SQL:

    DELETE ALIAS1
    FROM TABLE1 ALIAS1
    INNER JOIN TABLE2 ALIAS2
    ON ALIAS2.ID = ALIAS1.ID

    please don't tell me I have to use a 'WHERE EXISTS' subquery!

    Ta in advance.

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    u have to use a subquery. Either use the where exists or where column_name in syntax.

    Using the IN operator it could look like:

    delete
    from table1
    where table1.table2_id in
    ( select table2.id
    from table2
    where ........<any conditions on table 2 go here>)

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    Oracle 9i supports ANSII SQL. So I would think that your delete statement would work on a 9i instance.

    If your 8i or before you will need to use Subquery.

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Originally posted by carloa
    Oracle 9i supports ANSII SQL. So I would think that your delete statement would work on a 9i instance.

    If your 8i or before you will need to use Subquery.
    Don't know about that. Just been running through the version 9 documentation of Oracle, and the syntax diagram of the DELETE statement does not seem to include that feature. Moreover, MSSQL booksonline explicitely state that the ADDITIONAL FROM clause in
    DELETE from <TABLE_NAME> from <TABLE_SOURCE>
    is a Transact-SQL extension.

Posting Permissions

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