This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Nov 13, 2012 12:33 PM by AlbertoFaenza RSS

ORA-00918: column ambiguously defined

969952 Newbie
Currently Being Moderated
Hi All,

I have written a query as follows.
  select col1
   from tab1 t1,tab2 t2
      t1.col=t2.col; 
but I am getting the below error. Please have a look and advice.

ORA-00918: column ambiguously defined

Thanks,
Ora.
  • 1. Re: ORA-00918: column ambiguously defined
    Solomon Yakobson Guru
    Currently Being Moderated
    Your query joins two tables. Since both tables have column col1 Oracle doesn't know if you have in mind col1 from tab1 or from tab2. Therefore you need to qualify which col1 you have in mind. E.g:
    select t1.col1
       from tab1 t1,tab2 t2
          t1.col=t2.col;
    Or:
    select t2.col1
       from tab1 t1,tab2 t2
          t1.col=t2.col;
    SY.
  • 2. Re: ORA-00918: column ambiguously defined
    963739 Newbie
    Currently Being Moderated
    The col1 column was present in both tables and if you doesn't specify from which table you want to retrieve then it will throw

    ORA-00918: column ambiguously defined

    use below query
    select t1.col1, t2.col1
       from tab1 t1,tab2 t2
          t1.col=t2.col;
  • 3. Re: ORA-00918: column ambiguously defined
    Frank Kulash Guru
    Currently Being Moderated
    966949 wrote:
    Hi All,

    I have written a query as follows.
      select col1
    from tab1 t1,tab2 t2
    t1.col=t2.col; 
    but I am getting the below error. Please have a look and advice.

    ORA-00918: column ambiguously defined
    Solomon showed how to fix the problem.

    In case you're interested in why it occurs:
    If the same column name occurs in 2 (or more) tables in the same query, then syntax requires you to Qualify the column name with the table name (or alias), so that there's no possible confusion about which column you mean. This is a syntax requirement, it has nothing to do with the data in the column. In the example you gave (and in Solomon's answer) it doesn't matter which table name you use, because you know the values are going to be equal, but you have to qualify it all the same.

    Actually, it's a good programming practice to qualify all columns in a multi-table query, even if the column name only occurs in one table. It makes the code easier to understand and to maintain, and it also makes the code less likely to fail if you add or rename columns.
  • 4. Re: ORA-00918: column ambiguously defined
    John Spencer Oracle ACE
    Currently Being Moderated
    Frank Kulash wrote:
    ...
    In the example you gave (and in Solomon's answer) it doesn't matter which table name you use, because you know the values are going to be equal, but you have to qualify it all the same.
    Frank:

    Nice explanantion (and correct), but I think you need to get new spectacles :-)

    The OP is selecting col1 and joining on col.

    John

    Edited by: John Spencer on Nov 9, 2012 3:11 PM
    Fixed the messed up quoting
  • 5. Re: ORA-00918: column ambiguously defined
    Frank Kulash Guru
    Currently Being Moderated
    Hi, John,
    John Spencer wrote:
    ... but I think you need to get new spectacles :-)

    The OP is selecting col1 and joining on col.
    You're right; it would be a much more interesting situation if there was only column involved, I totally missed that.

    And you're tight; J do nerd new spextacles. I just hop I'm npt mqking qny typnng weeiea.
  • 6. Re: ORA-00918: column ambiguously defined
    Solomon Yakobson Guru
    Currently Being Moderated
    John Spencer wrote:
    In the example you gave (and in Solomon's answer) it doesn't matter which table name you use, because you know the values are going to be equal, but you have to qualify it all the same.
    John:
    select col<font color=red size=2>1</font><br>
    from tab1 t1,tab2 t2
    t1.col=t2.col;
    SY.
  • 7. Re: ORA-00918: column ambiguously defined
    969952 Newbie
    Currently Being Moderated
    Hi All,

    Thanks for your quick response.

    I have written the query as follows .

    SELECT COL1
    FROM tab1 t1
    WHERE t1.col1 in (SELECT tab2.col FROM t2 ) -- It is working fine..

    but I want to update the col1 .. I tried with the Update statement as follows
     Update t1set col1=
                 (  SELECT COL1
    FROM tab1 t1
    WHERE t1.col1 in (SELECT tab2.col  FROM  t2 ) 
    ); 
    getting the below error :

    ORA-01427: single-row subquery returns more than one row

    please share your thoughts to solve this prob.

    Thanks.
  • 8. Re: ORA-00918: column ambiguously defined
    ranit B Expert
    Currently Being Moderated
    966949 wrote:
    but I want to update the col1 .. I tried with the Update statement as follows
     Update t1set col1=
    (  SELECT COL1
    FROM tab1 t1
    WHERE t1.col1 in (SELECT tab2.col  FROM  t2 ) 
    ); 
    Please explain what are you tryin to achieve.
    I mean what you want to update and with what data?
  • 9. Re: ORA-00918: column ambiguously defined
    969952 Newbie
    Currently Being Moderated
    am trying to update one particular column values with matching column from two tables. after retrieving data from those two tables it ran successfully and got many records now I want to update the Column1 with these matched records.

    please let me know if you need more information.
  • 10. Re: ORA-00918: column ambiguously defined
    ranit B Expert
    Currently Being Moderated
    966949 wrote:
    am trying to update one particular column values with matching column from two tables. after retrieving data from those two tables it ran successfully and got many records now I want to update the Column1 with these matched records.

    please let me know if you need more information.
    Please post some data what is fetched by the SELECT query.
    got many records now I want to update the Column1 with these matched records.
    It's still unclear/confusing, coz how can you update 1 column with multiple records ??

    Please post some sample data.
  • 11. Re: ORA-00918: column ambiguously defined
    John Spencer Oracle ACE
    Currently Being Moderated
    Sy:

    Actually I was quoting Frank, but messed up the quotes and did not notice.

    John
  • 12. Re: ORA-00918: column ambiguously defined
    969952 Newbie
    Currently Being Moderated
    UPDATE tab1 SET Col1 =
       ( SELECT col1 FROM tab1 t1I
            WHERE t1.col2_NUM in 
             ( SELECT t2.Col1  FROM tab2 t2 )
       ); 
    yeah.. as you said we can't update single row with multiple records. the above select is returning more than 100 records. So now I want to update Col1 with these matched records.

    Hope you got it. Still need more info please let me know.

    Above Select statement is retuning the Output as

    Col1
    -----
    1
    2
    3
    4
    89
    45
    32
    ...
    ..
    now I want to update these values.

    Thanks.
  • 13. Re: ORA-00918: column ambiguously defined
    ranit B Expert
    Currently Being Moderated
    Dude... this is the only reason why your query is throwing error ORA-01427: single-row subquery returns more than one row

    You can't update a column with multiple values. Unless you concatenate all of them into a single value (using String Aggregation).
    ex. "1,2,3,...,100" as a single value after aggregation and now u can update the column value with this.

    Hope you understand.
    Ranit B.
  • 14. Re: ORA-00918: column ambiguously defined
    969952 Newbie
    Currently Being Moderated
    Any other way to resolve this case?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points