This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Nov 13, 2012 12:33 PM by AlbertoFaenza Go to original post RSS
  • 15. Re: ORA-00918: column ambiguously defined
    Sg049 Explorer
    Currently Being Moderated
    UPDATE tab1 SET Col1 =
    +(SELECT col1 FROM tab1 t1I+
    WHERE t1.col2_NUM in
    +( SELECT t2.Col1 FROM tab2 t2 )+
    +);+



    Are you updating the tab1.col1 with the same value from tab1.col1? Would you mind posting the create and insert statements and column name you want to update from?
  • 16. Re: ORA-00918: column ambiguously defined
    969952 Newbie
    Currently Being Moderated
    Hi,

    Please find the below details.

    SELECT DEPT_ID
    FROM table1 t1
    WHERE t1.d_NUM in
    ( SELECT t2.SA_NUM FROM table2 t2 );

    I got some data after executing the above query. It is having thousands of data. Now I want to update the DEPT_ID with matched t2.SA_NUM&t1.D_NUM.

    Please help me to write any pl/sql block for the above requirement and let me know if you required any other information.

    Thanks.
  • 17. Re: ORA-00918: column ambiguously defined
    AlbertoFaenza Expert
    Currently Being Moderated
    966949 wrote:
    Hi,

    Please find the below details.

    SELECT DEPT_ID
    FROM table1 t1
    WHERE t1.d_NUM in
    ( SELECT t2.SA_NUM FROM table2 t2 );

    I got some data after executing the above query. It is having thousands of data. Now I want to update the DEPT_ID with matched t2.SA_NUM&t1.D_NUM.

    Please help me to write any pl/sql block for the above requirement and let me know if you required any other information.

    Thanks.
    Hi,

    when you put some code please enclose it between two lines starting with {noformat}
    {noformat}
    i.e.:
    {noformat}
    {noformat}
    SELECT ...
    {noformat}
    {noformat}
    
    Also try to be clear and post table structure and sample data if possible.
    
    
    I want to update the DEPT_ID with matched t2.SA_NUM&t1.D_NUM.
    It's not clear from this statement which value you want to assign to DEPT_ID column while doing the update. Regards. Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 18. Re: ORA-00918: column ambiguously defined
    969952 Newbie
    Currently Being Moderated
    Hi,

    I have data as follows
    SELECT DEPT_ID from TABLE1 T1;
    
    O/P
    
    DEPTNO  DNAME  DEPT_ID
    --------------------------------------
    10          Analysts     1001
    20          Clercks       1004
    30          Managers    1006
    40          ..........
    ..
    ..
    Select * from table2 t2;
    
    EMPNO  ENAME  JOb ENUM
    --------------------------------------
    1            A          X     70
    2            B          Y     90
    3            C          Z      30
    4            D          R     1001
    5            E          S      1006
    6            F          T     1004
    7            G          U     1001
    8            H          V     1006
    
    {Code}
    
    Columns DEPT_ID and ENUM are common columns from both the tables.No I want to Update the DEPT_ID from table1 with mathed records ( i.e matched DEPT_ID and ENUM) from the two tables.
    
    Thanks.
    
    Edited by: 966949 on Nov 13, 2012 6:58 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 19. Re: ORA-00918: column ambiguously defined
    AlbertoFaenza Expert
    Currently Being Moderated
    Well,

    I must be stupid but from your message:
    TABLE1 columns:
    DEPTNO
    DNAME
    DEPT_ID
    
    TABLE2 columns:
    EMPNO
    ENAME
    JOb 
    ENUM
    Columns DEPT_ID and ENUM are common columns from both the tables
    I see DEPT_ID only in TABLE1 and ENUM only in TABLE2. Why you say that they are in common?

    I guess that you probably mean that the are the column used to join the tables (enum=dept_id)
    I want to Update the DEPT_ID from table1 with mathed records ( i.e matched DEPT_ID and ENUM) from the two tables.
    What do you want to update and which value????
    DEPT_ID is already updated.

    Be clear, post sample data and expected output.
    Please read SQL and PL/SQL FAQ

    Regards.
    Al
  • 20. Re: ORA-00918: column ambiguously defined
    969952 Newbie
    Currently Being Moderated
    I didn't mean it.

    The two tables have mapped values, I mean to say is, some Values in the DEPT_ID is equal and some are not equal to ENUM column.

    so Now I want to update the column DEPT_ID from Table1 with matched values from the two tables. Because the values which are existed in one table is not existed in the other table. Now I am trying to update the column DEPT_ID with matched values from the both tables. Please have a look into the below query which I have written
    SELECT DEPT_ID
                 FROM Table1 t1
                  WHERE t1.dept_id in 
             ( SELECT t2.ENUM  FROM table2 t2)
    
    { code}
    
    by executing this query I got 1000 records. so now I want to update DEPT_ID from table1,table2 with matched DEPT_ID and ENUM columns. please let me know how to write write PL/SQL block for this.
    
    Edited by: 966949 on Nov 13, 2012 7:39 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 21. Re: ORA-00918: column ambiguously defined
    AlbertoFaenza Expert
    Currently Being Moderated
    966949 wrote:
    I didn't mean it.

    The two tables have mapped values, I mean to say is, some Values in the DEPT_ID is equal and some are not equal to ENUM column.

    so Now I want to update the column DEPT_ID from Table1 with matched values from the two tables. Because the values which are existed in one table is not existed in the other table. Now I am trying to update the column DEPT_ID with matched values from the both tables. Please have a look into the below query which I have written
    SELECT DEPT_ID
    FROM Table1 t1
    WHERE t1.dept_id in 
    ( SELECT t2.ENUM  FROM table2 t2)
    by executing this query I got 1000 records. so now I want to update DEPT_ID from table1,table2 with matched DEPT_ID and ENUM columns. please let me know how to write write PL/SQL block for this.

    Edited by: 966949 on Nov 13, 2012 7:39 AM
    Sorry,

    I must be tired but still did not get what you mean.
    Please post create table, insert statement and expected output as specified in SQL and PL/SQL FAQ.

    By executing this query:
    SELECT DEPT_ID
                 FROM Table1 t1
                  WHERE t1.dept_id in 
             ( SELECT t2.ENUM  FROM table2 t2)
    you will get rows from Table1 which have DEPT_ID column matching at least in one row ENUM column in Table2.
    I want to update DEPT_ID from table1,table2 with matched DEPT_ID and ENUM columns
    Please elaborate your sentence.
    I still don't understand how you want to update DEPT_ID.
    Please provide sample data and expected output.

    Regards.
    Al

    Edited by: Alberto Faenza on Nov 13, 2012 6:33 PM
    Clarification added
  • 22. Re: ORA-00918: column ambiguously defined
    969952 Newbie
    Currently Being Moderated
    Hi,

    for the clarification

    table1 is having 789 records ( distinct DEPT_ID)

    table2 is having around 98087 records ( ENUMS)

    In table1 there is a column called DP.

    I want to update the column DP with matching records of " table1.DEPT_ID = table2.ENUM. ".
  • 23. Re: ORA-00918: column ambiguously defined
    SomeoneElse Guru
    Currently Being Moderated
    Merge into table1
    Using table2
    On (table1.dept_id = table2.enums)
    When matched then update
    Set table1.dp = table2.dp;
  • 24. Re: ORA-00918: column ambiguously defined
    969952 Newbie
    Currently Being Moderated
    Hi,

    after using the above Merge statement am getting " ORA-00001: unique constraint (SCHEMA_NAME.table1_PK) violated " message. Please have a look.

    Thanks.
  • 25. Re: ORA-00918: column ambiguously defined
    AlbertoFaenza Expert
    Currently Being Moderated
    966949 wrote:
    Hi,

    after using the above Merge statement am getting " ORA-00001: unique constraint (SCHEMA_NAME.table1_PK) violated " message. Please have a look.

    Thanks.
    We cannot know what you are trying to do if you don't post:
    Create table
    Insert statements
    Expected output

    This is the third time I ask you that.
    The message above is just saying that you try to update a value in table1 which is expected to be unique.

    You never posted your table structure and create table statement.
    You never posted sample data and insert statement.
    You have not been clear about your expected output.

    Quite difficult to help you in this way.

    Regards.
    Al

    Edited by: Alberto Faenza on Nov 13, 2012 8:24 PM
  • 26. Re: ORA-00918: column ambiguously defined
    969952 Newbie
    Currently Being Moderated
    Hi All,

    Thanks a lot .The issue got resolved.

    Many Thanks for your support and patience.

    Regards,
    Ora.
  • 27. Re: ORA-00918: column ambiguously defined
    AlbertoFaenza Expert
    Currently Being Moderated
    966949 wrote:
    Hi All,

    Thanks a lot .The issue got resolved.

    Many Thanks for your support and patience.

    Regards,
    Ora.
    If so, please mark your question as answered. So far you haven't marked even one:
    Handle:      966949  
    Status Level:      Newbie
    Registered:      Oct 22, 2012
    Total Posts:      25
    Total Questions:      6 (6 unresolved)
    Regards.
    Al
1 2 Previous Next

Legend

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