Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Philadelphia
    Posts
    8

    Angry Unanswered: Outer join in update/insert statement

    I'm relatively new to Oracle and I'm trying to get used to everything. The problem I'm having is using an outer join in an update/insert statement.

    In SQL server I could just do:

    INSERT INTO tableA A (A.ID)
    SELECT tqableB.ID
    FROM tableB LEFT OUTER JOIN
    tableA ON tableB.ID= A.ID


    I've tried in Oracle:

    INSERT INTO tableA A (A.ID)
    SELECT tqableB.ID
    FROM tableA, tableB
    WHERE tableB.ID= A.ID(+)

    and i get the following error:
    an outer join cannot be specified on a correlation column.

    How can I accomplish this inn Oracle?

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Outer join in update/insert statement

    Originally posted by cavada1

    INSERT INTO tableA A (A.ID)
    SELECT tqableB.ID
    FROM tableB LEFT OUTER JOIN
    tableA ON tableB.ID= A.ID
    The select statement you quote seems to be equivalent to a simple

    SELECT SELECT tqableB.ID FROM tableB

    since both return the same result: a list of IDs from tableB. Why would you need to use join?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Outer join in update/insert statement

    FYI Oracle 9i supports Ansii joins. So if your on 9i, your originaly Sql-Server queries will work if the tables are the same.

    But I agree, why would you be inserting records using an outer join from A to B resulting in all records from B? Why bother outer joining?

Posting Permissions

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