Re: Simple (?) SQL Query
In article <3o6oke$1kp$1@mhafc.production.compuserve.com>,
100355.122@CompuServe.COM says...
>
>In Melbourne our telephone numbers will soon change from
>(03)xxx-xxxx to (039)xxx-xxxx format. We have thousands of these
>numbers stored in an informix database as part of a fault tracking
>system, and we need to change them. I have tried a few SQL
>queries, but I cannot seem to get the "SET" clause to insert the 9
>into the existing number. I am using:
>update site set phone=????
>where site.phone like "03%";>The ???? is where I am still stumped.
>Any ideas?
>
>Thanks,
>Trevor Staats
>CS 100355,123
>
>--
>Trevor Staats - Australis Microcomputer
>1 Keith Campbell Court
>Scoresby VIC 3179 AUSTRALIA
Hello,
i would suggest two ways to do the job:
1) Assuming the field phone has constant length you might try:
update site set phone = "039" || phone [3, 10]
where site.phone like "03%";
2) Unload only the field phone. Manipulate the unload-file using "awk"
(see especially the function "substr"). Create a new table with the
two
fields original number and new number. Then load from the changed file
into the new table, say new. Finally you can write:
update site set phone = (select new_phone from phone where site.phone
= new.phone)
where site.phone like "03%";
Hope this helps.
Many Greetings
Willi
------------------------------------------------------------------
Willi Kwiotek A Joke from the Net-News:
kwiotek@hacon.de It's not bug - it's a feature