Re: Merge problem

Mohammad wrote:
> table used: 'emp' of "scott" schema
> i create a table "Jango" it's a copy of "emp" table and this table has no record
> i gave the following statment on 9i DB Server and fail to exc.
> Please tell me what's wrong in it
>
> MERGE INTO jango D
> USING ( SELECT empno,ename from emp ) e
> ON (d.empno = e.empno)
> WHEN MATCHED THEN UPDATE SET D.EMPNO = E.EMPNO, D.ENAME = E.ENAME
> WHEN NOT MATCHED THEN INSERT (D.EMPNO,D.ENAME)
> VALUES (E.EMPNO,E.ENAME)
>
>
> ERROR at line 3:
> ORA-00904: "D"."EMPNO": invalid identifier
>
> Thanks
> Mohammad

try it like:
  MERGE INTO jango D

     USING ( SELECT empno,ename from emp ) e
     ON (d.empno = e.empno)
     WHEN MATCHED THEN UPDATE SET D.ENAME = E.ENAME
    WHEN NOT MATCHED THEN INSERT (D.EMPNO,D.ENAME)
     VALUES (E.EMPNO,E.ENAME);

I believe it is because you cannot update the key that you are selecting   on (this may not work either, because you are also updating the ename). I'm not in a position to test this right now, though. Received on Wed Aug 20 2003 - 17:18:39 CDT