Re: UPDATE using LIKE?

In article <39docp$1e4_at_ctsad5.cts> phil_at_mtu.edu (Phil Huber) writes:
> bjgelly_at_iccgcc.cs.hh.ab.com wrote:
> : is it possible to update a set of records that require wildcards to select?
> : for example, i would like to update the records returned from the following
> : select statement:
> :
> : select name from brian.table where name like 'A__B%'
> :
> : i would like to change the 'A' to a 'B'. the update command does not
> : appreciate the 'like' command. i'd prefer to do this in a single query.
> : thanks.
> :
> Try this:
>
> update brian.table
> set name = replace(name,'A__','B__')
> where name like 'A__B%'
> --
> +---------------------------------------------------------------------+
> | Phillip Huber Analyst/Programmer Michigan Technological Univ. |
> | Internet: phil_at_mtu.edu Phone:(906)487-2223 Fax:(906)487-2521 |
> +---------------------------------------------------------------------+

I thought this was too good to be true. If the character is always in the same column then you could try the 'will_work' argument below. A test:

Connected to:
ORACLE RDBMS V6.0.36.7.1, transaction processing option - Production PL/SQL V1.0.36.2.2 - Production

18:10:16 system_at_DEV> r
  1 select replace(table_name, 'I_C', 'A_B') wont_work,   2 'B'||substr(table_name, 2) will_work   3 from all_tables
  4* where table_name like 'I_C%'
more...

WONT_WORK WILL_WORK
--------------- ---------------

INCEXP          BNCEXP
INCVID          BNCVID
INCFIL          BNCFIL

--
David R. Thrash
dthrash_at_sbctri.sbc.com           Compuserve:  76217,1304

Thrash & Company
9102 Garland Road, Suite 216I    Voice:     214.327.1972
Dallas, Texas 75218              Facsimile: 214.327.3510