Re: basic sql help please

"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/