RE: SQL Update ?

[rob@xxxxxxxxx]

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
You're welcome.

Also, as an alternative, or addition to, the 'else' you could use a where
clause.  Example:

update rob/casetest
set testfld=
case
when substr(testfld,1,3)='300'
 then ('400' || substr(testfld,4,7))
when substr(testfld,1,3)='310'
 then ('410' || substr(testfld,4,7))
end
Where substr(testfld,1,3) in ('300', '310')

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




Mark Allen <allenmark@nu-z.net>
Sent by: midrange-l-admin@midrange.com
04/18/2002 09:58 AM
Please respond to midrange-l


        To:     midrange-l@midrange.com
        cc:
        Fax to:
        Subject:        RE: SQL Update ?


Thanks Rob, not huge files but still better

Mark Allen
I.S. Manager
Wilkes Telephone & Electric
A Dycom Company
Phone: (706) 678-9565
Email: allenmark@nu-z.net
http://www.nu-z.net




:-----Original Message-----
:From: midrange-l-admin@midrange.com
:[mailto:midrange-l-admin@midrange.com] On Behalf Of rob@dekko.com
:Sent: Thursday, April 18, 2002 8:47 AM
:To: midrange-l@midrange.com
:Subject: Re: SQL Update ?
:
:
:This is a multipart message in MIME format.
:--
:[ Picked text/plain from multipart/alternative ]
:One update:
:
:update rob/casetest
:set testfld=
:case
:when substr(testfld,1,3)='300'
: then ('400' || substr(testfld,4,7))
:when substr(testfld,1,3)='310'
: then ('410' || substr(testfld,4,7))
:else
: testfld
:end
:
:Previously I forgot the 'else'.  I didn't fail in my earlier
:test because I had all records covered.  But when I added a
:record that didn't meet the when's it horfed.  The else fixed it.
:
:This SQL allows you to update a file in one pass.  If it is a
:large file, this may be significant.
:
:Rob Berendt
:--
:"They that can give up essential liberty to obtain a little
:temporary safety deserve neither liberty nor safety." Benjamin Franklin
:
:
:
:
:rob@dekko.com
:Sent by: midrange-l-admin@midrange.com
:04/18/2002 08:33 AM
:Please respond to midrange-l
:
:
:        To:     midrange-l@midrange.com
:        cc:
:        Fax to:
:        Subject:        Re: SQL Update ?
:
:
:This is a multipart message in MIME format.
:--
:[ Picked text/plain from multipart/alternative ]
:select * from rob/casetest
:
:KEYFLD  TESTFLD
:  A     300-12
:  B     310-23
:
:update rob/casetest
:set testfld=
:case
:when substr(testfld,1,3)='300'
: then ('400' || substr(testfld,4,7))
:when substr(testfld,1,3)='310'
: then ('410' || substr(testfld,4,7))
:end
:
:select * from rob/casetest
:KEYFLD  TESTFLD
:  A     400-12
:  B     410-23
:
:
:Rob Berendt
:--
:"They that can give up essential liberty to obtain a little
:temporary safety deserve neither liberty nor safety." Benjamin Franklin
:
:
:
:
:Mike.Collins@syan.co.uk
:Sent by: midrange-l-admin@midrange.com
:04/18/2002 08:04 AM
:Please respond to midrange-l
:
:
:        To:     midrange-l@midrange.com
:        cc:
:        Fax to:
:        Subject:        Re: SQL Update ?
:
:
:
:You can certainly do it one at a time:
:
:UPDATE myfile SET myfield = '341' || substr(myfield,4,14)
:WHERE substr(myfield,1,3) = '300'
:
:_______________________________________________
:This is the Midrange Systems Technical Discussion (MIDRANGE-L)
:mailing list To post a message email: MIDRANGE-L@midrange.com
:To subscribe, unsubscribe, or change list options,
:visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
:or email: MIDRANGE-L-request@midrange.com
:Before posting, please take a moment to review the archives
:at http://archive.midrange.com/midrange-l.
:
:
:
:_______________________________________________
:This is the Midrange Systems Technical Discussion (MIDRANGE-L)
:mailing list To post a message email: MIDRANGE-L@midrange.com
:To subscribe, unsubscribe, or change list options,
:visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
:or email: MIDRANGE-L-request@midrange.com
:Before posting, please take a moment to review the archives
:at http://archive.midrange.com/midrange-l.
:
:
:
:_______________________________________________
:This is the Midrange Systems Technical Discussion (MIDRANGE-L)
:mailing list To post a message email: MIDRANGE-L@midrange.com
:To subscribe, unsubscribe, or change list options,
:visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
:or email: MIDRANGE-L-request@midrange.com
:Before posting, please take a moment to review the archives
:at http://archive.midrange.com/midrange-l.
:
:


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.