1 2 Previous Next 27 Replies Latest reply: Nov 13, 2012 2:33 PM by AlbertoFaenza RSS

    ORA-00918: column ambiguously defined

    969952
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              Sy:

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

                              John
                              • 12. Re: ORA-00918: column ambiguously defined
                                969952
                                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
                                  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
                                    Any other way to resolve this case?
                                    1 2 Previous Next