ora-00904 and merge
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: ora-00904 and merge

  1. #1
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82

    ora-00904 and merge

    Hi,

    I am getting an Ora-00904 error ("TEST"."ENA": invalid identifier) when I try to issue this merge statement

    merge into test
    using (select ena from test1) t1
    on (t1.ena = test.ena)
    when matched then update set test.ena = t1.ena
    when not matched then insert (test.ena) values (t1.ena)

    if I issue this statement then I don't get the error and I don't get the right result as well

    merge into test
    using (select ena from test1) t1
    on (t1.ena = 1)
    when matched then update set test.ena = t1.ena
    when not matched then insert (test.ena) values (t1.ena)


    Here are structures and content of tables test and test1
    desc test
    Name Null? Type
    ------------ -------- ------------
    ENA VARCHAR2(10)


    desc test1
    Name Null? Type
    ------------ -------- ------------
    ENA VARCHAR2(10)


    select * from test;
    ENA
    ----------
    1
    2
    3
    4
    5

    select * from test1;
    ENA
    ----------
    3
    4
    5
    6
    7
    8

  2. #2
    Join Date
    Aug 2001
    Posts
    5

    try!!!!!!

    try it out!

    --why dont u try giving alias name for table test as

    merge into test t
    using (select ena from test1) t1
    on (t1.ena = t.ena)
    when matched then update set t.ena = t1.ena
    when not matched then insert values (t1.ena)
    let us help eachother

  3. #3
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82
    Now I am getting

    ERROR at line 3:
    ORA-00904: "T"."ENA": invalid identifier

    OK what is the difference between this two statements??

    merge into test t1
    using (select ena from test1) t2
    on (t1.ena = t2.ena)
    when matched then update set t1.ena = t2.ena
    when not matched then insert (t1.ena) values (t2.ena)

    and

    MERGE INTO bonuses D
    USING (SELECT employee_id, salary, department_id FROM employees
    WHERE department_id = 80) S
    ON (D.employee_id = S.employee_id)
    WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
    WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
    VALUES (S.employee_id, S.salary*0.1);

    Well the second one works. From what I can see the syntax is OK in the first query so the problem is somewhere else.

    I would realy like to know where the problem is, becouse I just can't figure it out.

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    It's pretty simple when you know know what it is.

    You cannot put a column that is in the ON clause into the UPDATE statement. This means that this will fail:

    merge into tab1 t1
    using (select * from tab1) t2
    on (t1.id = t2.id)
    when matched then
    update set t1.id = t2.id, -- Dodgy line
    t1.description = t2.description
    when not matched then
    insert (id, description)
    values (t2.id, t2.description);

    but this will work:

    merge into tab1 t1
    using (select * from tab1) t2
    on (t1.id = t2.id)
    when matched then
    update set t1.description = t2.description
    when not matched then
    insert (id, description)
    values (t2.id, t2.description);

    The impact of this is that there are some situations where you just can't use a merge. Namely when you need to update a column that you are using to identify the record in the ON clause.

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  5. #5
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82
    I can see clearly now the rain is gone!

    Thank you

Posting Permissions

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


Click Here to Expand Forum to Full Width