Home » SQL & PL/SQL » SQL & PL/SQL » Merge Statement
Merge Statement [message #194726] Mon, 25 September 2006 00:13 Go to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi,
Merge statement is giving me an error :

MERGE INTO ABC D
USING (SELECT A.ID , C.INDV_ID
FROM DEF A , GHI B , ABC C
WHERE A.ID = B.MANAGERID
AND LPAD(A.ID,8,0) = PKG_ENCRYPT_DECRYPT.FNC_DECRYPT(C.INDV_UEI,Cool) S
ON (LPAD(S.ID,8,0) = PKG_ENCRYPT_DECRYPT.FNC_DECRYPT(D.INDV_UEI,Cool)
WHEN MATCHED THEN UPDATE SET D.INDV_MNGR_ID = S.INDV_ID
WHEN NOT MATCHED THEN NULL;

The error is
ORA-00905: missing keyword

I am using Oracle 9i.
Actually i have to ignore NOT MATCHED .Remember i am using 9i.


Regards,
Vishal Gupta
Re: Merge Statement [message #194736 is a reply to message #194726] Mon, 25 September 2006 00:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why use merge if you want only an update? Rewrite your statement to an update-statement.
Re: Merge Statement [message #194771 is a reply to message #194726] Mon, 25 September 2006 04:08 Go to previous messageGo to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Hi,

Merge is meant for performing UPSERTS (UPDATE and INSERT) based on condition. If only update is your concern then usage is MERGE seems redundant.

Thanks
Re: Merge Statement [message #194776 is a reply to message #194726] Mon, 25 September 2006 04:36 Go to previous messageGo to next message
vijayanarayanan
Messages: 11
Registered: September 2006
Location: chennai
Junior Member
MERGE INTO DEPART A
USING DEPARTMENTS B
ON(A.DEPARTMENT_ID=B.DEPARTMENT_ID)
WHEN MATCHED THEN
NULL /* HERE YOU MUST PROVIDE AN UPDATE STMT MERGE MUST HAVE BOTH INSERT AND UPDATE /*
WHEN NOT MATCHED THEN
INSERT
VALUES(B.DEPARTMENT_ID,B.DEPARTMENT_NAME,B.MANAGER_ID,B.LOCATION_ID);

THE ERROR MESSAGE OF THIS IS:

NULL
*

ERROR at line 5:
ORA-00905: missing keyword
Re: Merge Statement [message #194798 is a reply to message #194776] Mon, 25 September 2006 06:43 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi All,
Can anybody help me in the syntax for Update statement ?
I am missing something .Here is what i am trying but i don't have the update priveleges for APA and it is giving me Insufficient privileges.

UPDATE (
SELECT A.ID , C.INDV_ID , A.MANAGERID
FROM APA.ABC A , DEF C
WHERE LPAD(A.MANAGERID,8,0) = PKG_ENCRYPT_DECRYPT.FNC_DECRYPT(C.INDV_UEI,Cool)
SET INDV_MNGR_ID = INDV_ID


Thanks and Regards,
Vishal Gupta
Re: Merge Statement [message #195715 is a reply to message #194798] Sun, 01 October 2006 13:20 Go to previous messageGo to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Vishal,

General syntax for Update is

UPDATE table_name SET col1=xyz,col2=pqr WHERE condition.

Do search the same on Google for more information.

Thanks
Re: Merge Statement [message #195718 is a reply to message #194798] Sun, 01 October 2006 15:45 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You are trying to update ABC for which you say you don't have update privileges and you are wondering why you are getting "insufficient privileges" ?
Previous Topic: Converting from normal table to partition table
Next Topic: SUM OVER
Goto Forum:
  


Current Time: Sat Dec 03 04:13:19 CST 2016

Total time taken to generate the page: 0.08205 seconds