Hi Rajeev,

In merge you should not use a column to update or in insert statement
Which is already used for Joining purpose. You remove column from join
condition or from update and insert, then it will work.

Thanks & Regards
Mujeeb

-----Original Message-----
From: Rajeev Sharma
[mailto:oracledba-ezmlmshield-x81090830.[Email address protected]
Sent: Thursday, September 06, 2007 5:26 PM
To: LazyDBA Discussion
Subject: PROBLEM WITH MERGE

Hi ALL,



I Am Facing a Problem With Merge Statement Like this is my merge
statement:



MERGE INTO reports_companies a
USING reports_companies_backup b
ON (a.cano = b.cano AND a.REPORT_ID = b.REPORT_ID)
WHEN MATCHED THEN
UPDATE
SET A.CUSTOMER_REPORT_ID =B.CUSTOMER_REPORT_ID ,
A.BUSINESS_UNIT =B.BUSINESS_UNIT,
A.STATUS = B.STATUS,
A.REPORT_ID = B.REPORT_ID,
A.CPOID =B.CPOID,
A.CANO = B.CANO,
A.CCOMPANY = B.CCOMPANY
WHEN NOT MATCHED THEN
INSERT (CUSTOMER_REPORT_ID,
BUSINESS_UNIT,STATUS,REPORT_ID,CPOID,CANO,CCOMPANY)
VALUES (b.CUSTOMER_REPORT_ID,
b.BUSINESS_UNIT,b.STATUS,b.REPORT_ID,b.CPOID,b.CANO,b.CCOMPANY);



When I am running this query it is giving me error "a.REPORT_ID Invalid
Identifire"



I have checked in both the table REPORT_ID column is there with same
length.



Please suggest me what should I do?



Thanks

Rajeev Sharma

(~_~)




--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



Oracle LazyDBA home page