RE: SQL question: why is one column updating but NOT the other?

Thanks Charles.

I have tested it several times with

AND docid = 'xxxxxx'

where 'xxxxx' is a key value and it seems to work great then!

Thanks for the tip!!

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Tuesday, September 16, 2014 3:46 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL question: why is one column updating but NOT the other?

When reference a column, it's from before the update starts...

Think of it as creating a temporary copy of the table whose values are used
where referenced in the UPDATE.

I'm actually surprised that it'd work differently with one row...

To use the new value in CHKDATE, you'd need to
update rjsimage/docs00
set chkdate =
timestamp(date(substr(keyword4,1,10)),time('00:00 AM')),
expdate =
timestamp(date(substr(keyword4,1,10)),time('00:00 AM')) + 2 years
where doctype2 = 'GENERALMTHSTMNT'

Charles

On Tue, Sep 16, 2014 at 4:14 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

I am running the following SQL statement in STRSQL (green screen) on V5r4.

update rjsimage/docs00
set chkdate =
timestamp(date(substr(keyword4,1,10)),time('00:00 AM')),
expdate = chkdate + 2 years

where doctype2 = 'GENERALMTHSTMNT'


Looking at each individual row, the CHKDATE column is updating perfectly.

However, the EXPDATE is not being updated, OR it is updating prior to the
new CHKDATE timestamp calculation.


If I include a WHERE clause targeting one specific row, then it works well.



Any idea why EXPDATE (expiration date) is not updating to be CHKDATE plus
two years?

Thanks!



______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.