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

update subsquery with Not = condition

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


ck9663
Expert 2.5K+
P: 2,878
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
Feb 9 '08 #2

P: 3
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!
Feb 9 '08 #3

code green
Expert 100+
P: 1,726
Well it looks like a DATE problem.
The only place I can see a date being used is here
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 'your dataestamp'
Feb 11 '08 #4

Post your reply

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