update sql with results subquery

[Matila]

I want to update data on table using the results of subquery.

for my sql below, I want to update region number of rows on table to the region number of rows that meet the condition with region ovrd = Y & terminate date = 999999.

I have tried:

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.

[deepuv04]

I want to update data on table using the results of subquery.

for my sql below, I want to update region number of rows on table to the region number of rows that meet the condition with region ovrd = Y & terminate date = 999999.

I have tried:

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.

hi,

try the following query ...

Update ACR B
Set B.region_NUMBER = A.region_NUMBER, B.region_OVRD_IND = 'Y'
From ACR B INNER JOIN
(select region_NUMBER from ACR
Where REGION_OVRD_IND <> 'Y' AND
TERMINATION_DTE <> 99999999) AS A ON B.AGENT_ID = A.AGENT_ID

thanks