469,917 Members | 1,631 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,917 developers. It's quick & easy.

update subsquery with Not = condition

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
3 1568
ck9663
2,878 Expert 2GB
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
Matila
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
1,726 Expert 1GB
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.

Similar topics

7 posts views Thread by Dave | last post: by
8 posts views Thread by Lauren Quantrell | last post: by
3 posts views Thread by Dan Berlin | last post: by
5 posts views Thread by Louis LeBlanc | last post: by
20 posts views Thread by Mark Harrison | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.