Re: Partial numeric field update in SQL

["Dennis Lovelady" <dlovelady@xxxxxxxx>]

SUBSTR creates a work field.... it's not a pointer into a data record and
therefore not the kind of thing that you can actually update with the SET
statement.

What you should be able to do though, is something like this:

UPDATE LIBRARY.FILE
SET FIELD = SUBSTR(DIGITS(FIELD,1,3)||'3'||SUBSTR(DIGITS(FIELD),5,2)
WHERE SUBSTR(DIGITS(FIELD),4,1) = '2';

Forgive if my concatenation syntax is incorrect.

HTH
Dennis






"Karl Keller" <kkeller@girling.com>@midrange.com on 12/11/2002 12:20:09 PM

Please respond to midrange-l@midrange.com

Sent by:    midrange-l-admin@midrange.com


To:    <midrange-l@midrange.com>
cc:
Subject:    Partial numeric field update in SQL


This is a multi-part message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Hi Group,

    I have a file with a 6 digit numeric field. In that field is a YYYYMM
date. I'm trying to update the year to the next year. 2002 to 2003.

    My statement is:

UPDATE LIBRARY.FILE
SET SUBSTR(DIGITS(FIELD),4,1) = '3'
WHERE SUBSTR(DIGITS(FIELD),4,1) = '2';

It's barfing on the ( between the first SUBSTR & DIGITS. The error I'm
getting is [SQL0104] Token ( was not valid.

However if I run the following Select statement:

Select * from LIBRARY.FILE Where (SUBSTR(DIGITS(FIELD),4,1)) = '2'

it executes successfully. I could have sworn that I have done updates like
this before! Can someone please explain to me why this doesn't work?