update subsquery with Not = condition

[Matila]

I am trying to create an update subsquery for the condition:
agent region region-ovrd-ind terminate-date
123 A Y 99999999 (active)
123 A Y 99999999 (active)
123 B N 20080101 (terminate)
123 B N 20080112 (terminate)

when the region-ovrd-id = Y & terminate-date is 9999999, then all the region should be same. In this case, update region to value of A & region-ovrd-id to Y

my update sql is:

Update ACR B
Set B.region_NUMBER = A.region_NUMBER, B.region_OVRD_IND = 'Y'
From ACR B
WHERE B.region_NUMBER NOT IN
(select A.region_NUMBER from ACR A
where B.AGENT_ID = A.AGENT_ID
AND A.REGION_OVRD_IND = 'Y' AND
A.TERMINATION_DTE = 99999999)

I am getting this error:


error
42601(-104)[IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "CURRENT_TIMESTAMP" was found following "DATE, B.TIME_STAMP =". Expected tokens may include: "(". SQLSTATE=42601
(0.02 secs)


Appreciate any suggestions. Thanks.

[ck9663]

I am trying to create an update subsquery for the condition:
agent region region-ovrd-ind terminate-date
123 A Y 99999999 (active)
123 A Y 99999999 (active)
123 B N 20080101 (terminate)
123 B N 20080112 (terminate)

when the region-ovrd-id = Y & terminate-date is 9999999, then all the region should be same. In this case, update region to value of A & region-ovrd-id to Y

my update sql is:

Update ACR B
Set B.region_NUMBER = A.region_NUMBER, B.region_OVRD_IND = 'Y'
From ACR B
WHERE B.region_NUMBER NOT IN
(select A.region_NUMBER from ACR A
where B.AGENT_ID = A.AGENT_ID
AND A.REGION_OVRD_IND = 'Y' AND
A.TERMINATION_DTE = 99999999)

I am getting this error:


error
42601(-104)[IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "CURRENT_TIMESTAMP" was found following "DATE, B.TIME_STAMP =". Expected tokens may include: "(". SQLSTATE=42601
(0.02 secs)


Appreciate any suggestions. Thanks.

try:

UPDATE ACR
set REGION = 'A'
where REGION_OVRD_IND = 'Y' AND TERMINATION_DTE = 99999999

remember, if this is done manually, you might not be able to roll this back. try doing a SELECT first and analyze the resultset first...

-- ck

[Matila]

Thanks for your suggestion.

However, I am doing this Update - subquery for unknown values off a table.
The above list of table values was an example of data.

Appreciate anymore suggestions!!!!!!!!!!!!????????????? Thanks!

[code green]

Well it looks like a DATE problem.
The only place I can see a date being used is here

A.TERMINATION_DTE = 99999999

But you are hiding what you are doing which makes it difficult.
Make sure you wrap the actual date in quotes

'your dataestamp'