Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2008

    Unanswered: Merge Statement error


    I am trying to run a merge statement but am recieiving an invalid identifier error even though the field in question does exist on the agency_cd_ctrl table

    merge into agency_cd_ctrl
    using (select b.user_id from seller_imp b inner join agency_cd_ctrl a
    on b.alias_user_id = a.user_id) c
    on (c.user_id = agency_cd_ctrl.user_id*)
    when matched then
    update set agency_cd_ctrl.user_id = c.user_id
    when not matched then
    insert (agency_cd_ctrl.user_id) values (null);

    * This is where the error occurs - ORA-00904: "AGENCY_CD_CTRL"."USER_ID": invalid identifier

    Is there an error with the syntax? I'm trying to run this against an Oracle 9i database...

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1
    I can't see anything wrong with it. This similar example works for me (but I'm on 10G):

    SQL> create table emp2 as select * from emp where rownum < 4
      2  /
    Table created.
    SQL> merge into emp2
      2  using (select * from emp) e
      3  on (e.empno = emp2.empno)
      4  when matched then 
      5    update set emp2.ename = e.ename
      6  when not matched then
      7    insert (emp2.empno, emp2.ename)
      8    values (e.empno, e.ename)
      9  /
    18 rows merged.

  3. #3
    Join Date
    Apr 2008

    Thanks for the response - i think the error message returned is misleading and i think the error may be caused by duplicate values in one of the tables.

Posting Permissions

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