Re: basic sql help please

Using LIKE is generally preferred. It has a better chance of using an index.

UPDATE table
SET phonenum = 'xxx'||SUBSTR(phonenum,4) WHERE phonenum LIKE 'yyy%';

"Ken Denny" <ken_at_kendenny.com> wrote in message news:Xns92D1675FA616Bkendenny_at_65.82.44.10...
> "Deuce" <rick_at_ricks-web.info> wrote in
> news:uu4cfc3mge4b2a_at_news.supernews.com:
>
> > Working in sql+ for 9i. I am trying to figure out some sql things.
> >
> > 1. Can I delete a record from multiple tables at once somehow
> > ex. Delete From table1, table2, table3 Where SSN =XXXXX;
>
> I don't believe this is possible. However if SSN is a primary key to one
> table and the other tables which have it have foreign key constraints on
> it, the foreigh key constraints can be defined with the "ON DELETE
> CASCADE" clause which will cause these records to automatically be
> deleted when the parent record is deleted.
> >
> > 2. how can I check the first three numbers of a field of 10 (it's a
> > phone num) to replace all instances of one set of numbers with another.
> > The purpose here is to replace the area code (digits 1-3) with a
> > different set for all instances in the relation.
>
> Use SUBSTR. Hopefully you have defined the phone number column as
> varchar2 rather than number. Columns which always contain numeric data
> should still be defined as varchar2 if you're not ever going to be using
> them in calculations, and I can't imagine what calculations anyone would
> ever want to do with phone number. Anyway you can
>
> UPDATE table
> SET phonenum = 'xxx'||SUBSTR(phonenum,4)
> WHERE SUBSTR(phonenum,1,3) = 'yyy';
>
> This will change all area code yyy phone numbers to area code xxx. If
> phonenum is defined as a number then in the above example replace
> "phonenum" with "TO_CHAR(phonenum)"
> >
> > Thanks for any assistance you can offer.
>
> Glad I could help.
>
> --
> Ken Denny
> http://www.kendenny.com/
>
Received on Mon Nov 25 2002 - 13:37:00 CST