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

["Stone, Joel" <Joel.Stone@xxxxxxxxxx>]

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.