RE: SQL Update ?

["Peter Colpaert" <Peter.Colpaert@xxxxxxxxxxxx>]

Mark,

indeed, it should be

update FILE set LCE = '341' || substr(LCE, 4) where substr(LCE, 1, 3)
= '300'

Too fast, too wrong.

Peter





Mark Allen <allenmark@nu-z.net>@midrange.com on 18/04/2002 16:12:36
Please respond to midrange-l@midrange.com
Sent by:  midrange-l-admin@midrange.com

To:   midrange-l@midrange.com

cc:

Subject:  RE: SQL Update ?


Gives me an invalid token at (

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 Peter Colpaert
:Sent: Thursday, April 18, 2002 8:02 AM
:To: midrange-l@midrange.com
:Subject: Re: SQL Update ?
:
:
:
:Mark,
:
:you could use the following statement:
:
:update FILE set substr(LCE, 1, 3) = '341' where substr(LCE, 1,
:3) = '300'
:
:Of course, you would have to do this 55 times (11 changes in 5
:files), but with the F9=Retrieve button a lot of typing can be avoided.
:
:HTH,
:
:Peter Colpaert
:Application Developer
:
:Honda Europe NV
:Langerbruggestraat 104
:B-9000 GENT
:Peter.Colpaert@honda-eu.com
:Tel: +32 9 2501 334
:Fax: +32 9 2501 231
:----------
:When 900 years old you reach, look as good, you will not. - Yoda
:----------
:
:
:
:
:Mark Allen <allenmark@nu-z.net>@midrange.com on 18/04/2002
:15:56:57 Please respond to midrange-l@midrange.com Sent by:
:midrange-l-admin@midrange.com
:
:To:   midrange-l@midrange.com
:
:cc:
:
:Subject:  SQL Update ?
:
:
:I have a need to change a field in a file as such.
:
:Field LCE defined as 17A   data example  300-00-00
:
:I need to change every record that has a 300 in the first 3
:pos to have 341 in the first 3 pos, leaving rest of the data the same.
:
:I have to do this across 5 files with 11 different changes
:(i.e 300 to 341, 330 to 349 etc.)
:
:I would really like to do with SQL if possible, can I with the
:above scenario?
:
: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
:
:
:
:
:
:_______________________________________________
: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.
:
:
:
:
:
:
:______________________________________________________________________
:The information contained in this communication is
:confidential and may be legally privileged. It is intended
:solely for the use of the individual or the entity to whom it
:is addressed and others authorised to receive it. If you have
:received it by mistake, please let the sender know by e-mail
:reply and delete it from your system. If you are not the
:intended recipient you are hereby notified that any
:disclosure, copying, distribution or taking any action in
:reliance of the contents of this information is strictly
:prohibited and may be unlawful. Honda Europe NV is neither
:liable for the proper and complete transmission of the
:information contained in this communication nor for any delay
:in its receipt.
:
:_______________________________________________
: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.







______________________________________________________________________


The information contained in this communication is confidential and may be
      legally privileged. It is intended solely for the use of the
      individual or the entity to whom it is addressed and others
      authorised to receive it. If you have received it by mistake, please
      let the sender know by e-mail reply and delete it from your system.

If you are not the intended recipient you are hereby notified that any
      disclosure, copying, distribution or taking any action in reliance of
      the contents of this information is strictly prohibited and may be
      unlawful.

Honda Europe NV is neither liable for the proper and complete transmission
      of the information contained in this communication nor for any delay
      in its receipt.