Home » SQL & PL/SQL » SQL & PL/SQL » Merge command error
Merge command error [message #312340] Tue, 08 April 2008 10:47 Go to next message
boopathi.it
Messages: 9
Registered: November 2007
Location: chennai
Junior Member

When i am trying to execute the below statement it showing the invalid identifier error ... please help me out on this.

MERGE INTO EMP A USING emp1 b ON ( A.SAL = b.sal AND a.ename = b.ename )
WHEN MATCHED THEN
UPDATE SET a.sal = a.sal + 500
WHEN NOT MATCHED THEN
INSERT (a.ename,a.sal) VALUES ('ORACLE',800)
Re: Merge command error [message #312342 is a reply to message #312340] Tue, 08 April 2008 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Search which identifier is invalid.
As we said in one of your previous post, either use standard scott's tables or post your table definitions.

In addition, copy and paste your session and format it.
But this has already been told you.

Regards
Michel
Merge in oracle [message #312343 is a reply to message #312340] Tue, 08 April 2008 10:51 Go to previous messageGo to next message
boopathi.it
Messages: 9
Registered: November 2007
Location: chennai
Junior Member

When i am trying to execute the below statement it showing the invalid identifier error ... please help me out on this.

MERGE INTO EMP A USING emp1 b ON ( A.SAL = b.sal AND a.ename = b.ename )
WHEN MATCHED THEN
UPDATE SET a.sal = a.sal + 500
WHEN NOT MATCHED THEN
INSERT (a.ename,a.sal) VALUES ('ORACLE',800)
Re: Merge in oracle [message #312346 is a reply to message #312343] Tue, 08 April 2008 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why do you post this multiple times?

Regards
Michel
Re: Merge command error [message #312520 is a reply to message #312340] Wed, 09 April 2008 02:26 Go to previous messageGo to next message
boopathi.it
Messages: 9
Registered: November 2007
Location: chennai
Junior Member

It saying b.ename is an invalid identifier but ename column is exist in emp1 table.

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Apr 9 03:00:35 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> MERGE INTO EMP A USING emp1 b ON ( A.SAL = b.sal AND a.ename = b.ename )
WHEN MATCHED THEN
UPDATE SET a.sal = a.sal + 500
WHEN NOT MATCHED THEN
INSERT (b.ename,b.sal) VALUES ('ORACLE',800)  2    3    4    5  ;
INSERT (b.ename,b.sal) VALUES ('ORACLE',800)
        *
ERROR at line 5:
ORA-00904: "B"."ENAME": invalid identifier

[Updated on: Wed, 09 April 2008 02:28]

Report message to a moderator

Re: Merge command error [message #312529 is a reply to message #312520] Wed, 09 April 2008 02:48 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Don't qualify your identifiers as for insert.
It is obvious for Oracle where you insert, and it is NOT in b.

Regards
Michel
Previous Topic: minus query
Next Topic: Moving table from one tablespace to other.
Goto Forum:
  


Current Time: Wed Feb 12 20:32:41 CST 2025