Re: Sql Stament error

You could also do:

update table
set columnA=replace(columnA, 'TRTK', 'PMTK') where columnA like ('TRTK%');

IMHO, cleaner because you don't have to know the length of anything.

Now, from the original post, I would suggest reading the sql documents or using them as reference. Your original statement was you had a string 'TRTK-1234567890'; Your update statement had what you wanted to change in the wrong order. You want to change TRTK to PMTK.
Your where clause (if written properly) searched for a row with PMTK% which 1)returned 0 rows because you used = 'PMTK%'

          2) would have returned 0 rows (if you used like) because your row started
with 'TRTK%';

If using wildcards, you should write
where column like ('TRTK%');

So, back to the documents for Oracle. They are a lot more useful than being a booster seat for your toddler ;-)

hth,
cindy

Anurag Varma wrote:

> You can't use
> where = 'PMTK%'
> since oracle will take % literally rather than as a wildcard character.
>
> One way is
>
> update table
> set columnA = 'TMTK' || substr(columnA, 5)
> where columnA LIKE 'PMTK%'
> /
>
> Anurag
>
> "Joshua Goodstein" <awing_pilot_at_yahoo.com> wrote in message
> news:924a90f8.0109061039.1e8d7a46_at_posting.google.com...
> > I have a table with a column ( columnA) it contains the values
> > TRTK-1234567890
> >
> > I want to update the values so that TRTK becomes PMTK i tried the
> > following and it failed:
> >
> > update table
> > set columnA='TRTK%'
> > where columnA='PMTK%';
> >
> > 0 rows updated
> > argg I know this is a menial thing but just frustrating
> >
> > Thanks in advance
> >
> > J
Received on Thu Sep 06 2001 - 14:38:17 CDT