View previous topic :: View next topic
|
Author |
Message |
GaganGarg
Active User
Joined: 31 Mar 2010 Posts: 134 Location: India
|
|
|
|
HI,
I m trying to update a table using MERGE in DB2 query.
Query looks like as given below:
Code: |
MERGE INTO AMTTABLE A
USING (SELECT CTRY_CODE,CO_CODE, ITEM_ID, AMT FROM AMTTABLE) B
ON ( B.CTRY_CODE = A.CTRY_CODE
AND B.CO_CODE = A.CO_CODE
AND B.ITEM_ID = A.ITEM_ID )
WHEN MATCHED
AND B.AMT <> A.AMT
THEN UPDATE SET A.AMT = B.AMT
WHEN NOT MATCHED THEN
SIGNAL SQLSTATE '70002'
|
I'm getting following sql error:
Code: |
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD SELECT. TOKEN VALUES
WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 80 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'00000050' X'000001FA' SQL DIAGNOSTIC INFORMATION
|
Db2 version used is 10.1.
Can anybody help me please to identify the issue? Thank you |
|
Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
I think that, in your case, you could just use an UPDATE to update the table...
But it seems you want to change all AMT values to one unique value for all rows having same CTRY_CODE, CO_CODE and ITEM_ID.
Is that what you really want to do ? |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3051 Location: NYC,USA
|
|
|
|
Gagan, The feature that you have tried might not be supported by DB2 on ZOS(Check with DBA) but it works perfectly fine over Linux. However, over ZOS supports USING (VALUES... as I tested. |
|
Back to top |
|
|
|