Problem with Update with multiple parameters

[sp1502]

I am trying to run an update query which uses multiple parameters in the where clause updating multiple rows based on those parameters:

UPDATE PROD.PLM103_AXXS
SET AD_ST = 'X'
WHERE (PL_L_R, L_XD_SEQ) in
((12283952,1),(12283952,3))


But it doesn't allow me to do so in SQL(DB2) giving me the following error message:

SQL0104N An unexpected token "(" was found following "_R, L_XD_SEQ) in (". Expected tokens may include: "<values> ".


Thanks in advance!!!

 

[LKBrwnDBA]

Try concatenate:

UPDATE PROD.PLM103_AXXS
   SET AD_ST = 'X'
 WHERE (PL_L_R||'+'||L_XD_SEQ)
    IN ('12283952+1','12283952+3')

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[sp1502]

Thanks LKBrwnDBA but it doesn't like the "||", I get this message now in DB2 v8.1:

SQL0440N No authorized routine named "||" of type "FUNCTION

 

[PHV]

UPDATE PROD.PLM103_AXXS
SET AD_ST = 'X'
WHERE PL_L_R = 12283952 AND L_XD_SEQ IN (1,3)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sp1502]

PHV, I actually truncated my actual query by putting in 2 values..I'll probably end up have > 50 so it may look like this:

UPDATE PROD.PLM103_AXXS
SET AD_ST = 'X'
WHERE (PL_L_R, L_XD_SEQ) in
((12283952,1),(12283952,3),(1583952,1),(12583552,7)....)


Your option would work if it was limited but unfortuately I
and doing this on a bigger scale

 

[carp]

How about

UPDATE PROD.PLM103_AXXS
   SET AD_ST = 'X'
 WHERE CONCAT(PL_L_R,CONCAT('+',L_XD_SEQ))
    IN ('12283952+1','12283952+3')

 

[r937]

WHERE PL_L_R = 12283952 AND L_XD_SEQ = 1
   OR PL_L_R = 12283952 AND L_XD_SEQ = 3
   OR PL_L_R = 1583952  AND L_XD_SEQ = 1
   OR PL_L_R = 12583552 AND L_XD_SEQ = 7
   OR ...

r937.com | rudy.ca

 

[PHV]

Rudy, you really mix AND & OR without parentheses ?

 

[r937]

Rudy, you really mix AND & OR without parentheses ?

yes, because i know what i'm doing ;-)

r937.com | rudy.ca

 

[sp1502]

carp - I received this message when I tried "
SQL0440N No authorized routine named "CONCAT" of type "FUNCTION "

r937 - thanks, I was trying to avoid typing all the fields in repeatedly like this, but this maybe the only solution!

 

[PHV]

The standard ANSI SQL concatenation operator is ||
So, reread the first reply and use the DB2 concatenation operator.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

i was pretty sure db2 supports ||

until now

p.s. i don't enjoy typing either, that's why i use a text editor which can do multi-line insertions at any column position

r937.com | rudy.ca

 

[carp]

If it is truly a problem unique to DB2, have you tried asking on the DB2 forum?

 

[r937]

carp, that's probably the best suggestion

notice how CONCAT got the same error message as ||

so it's probably something to do with the way the statement is consrtucted

r937.com | rudy.ca

 

[JarlH]

According to the SQL Validator it seems like DB2 doesn't support at least some of the following:

Result:
The following features outside Core SQL-200x (draft) are used:

F641, "Row and table constructors"
T051, "Row types"
F561, "Full value expressions"