By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,377 Members | 1,690 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,377 IT Pros & Developers. It's quick & easy.

update sql with results subquery

P: 3
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.
Feb 9 '08 #1
Share this Question
Share on Google+
1 Reply


deepuv04
Expert 100+
P: 227
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
Feb 11 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.