Home » SQL & PL/SQL » SQL & PL/SQL » Changing columns
icon5.gif  Changing columns [message #259915] Thu, 16 August 2007 18:02 Go to next message
kham2k
Messages: 34
Registered: May 2007
Member
Hi ALL

I got few columns which are varchar2(4) at the moment they have values like 01, 54, 39. in short they are two digits i want to convert them into four digits like 0001, 0054,0039.

What can I do?
Re: Changing columns [message #259916 is a reply to message #259915] Thu, 16 August 2007 18:03 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
use update statement.
icon5.gif  Re: Changing columns [message #259917 is a reply to message #259916] Thu, 16 August 2007 18:09 Go to previous messageGo to next message
kham2k
Messages: 34
Registered: May 2007
Member
there are about 200 million records. what will be the best way can we write a script or something?
Re: Changing columns [message #259918 is a reply to message #259915] Thu, 16 August 2007 18:11 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>what will be the best way can we write a script or something?
Best based upon what metric; fewest characters in script, fastest running, etc.?

Write SQL to write SQL.
icon5.gif  Re: Changing columns [message #259919 is a reply to message #259918] Thu, 16 August 2007 18:21 Go to previous messageGo to next message
kham2k
Messages: 34
Registered: May 2007
Member
fastest!!!!!!
Re: Changing columns [message #259921 is a reply to message #259915] Thu, 16 August 2007 18:32 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
update wrong_table set col1='00'||col1, col2='00'||col2, col3='00'||col3;
commit;

IMO, this exercise is a total waste of CPU cycles & disk space.
It would be more efficient to "add the zeros" on the SELECT side & could even be done as a VIEW.

[Updated on: Thu, 16 August 2007 18:41] by Moderator

Report message to a moderator

icon5.gif  Re: Changing columns [message #259923 is a reply to message #259921] Thu, 16 August 2007 19:03 Go to previous messageGo to next message
kham2k
Messages: 34
Registered: May 2007
Member
Do you think its the fastest way to do it?
I got about 200 million records...

Thanks
Re: Changing columns [message #259940 is a reply to message #259923] Thu, 16 August 2007 21:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The fastest way would be to rebuild the table

create table new_table as
select lpad(col1, 4, '0') as col1
,      lpad(col2, 4, '0') as col2
,      lpad(col3, 4, '0') as col3
from   old_table;

drop table old_table;
rename new_table to old_table;


You could also use NOLOGGING and PARALLEL hints to improve performance.

Ross Leishman
Re: Changing columns [message #259944 is a reply to message #259915] Thu, 16 August 2007 21:57 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
kham2k,

>Do you think its the fastest way to do it?
"fastest" is a relative term; not an absolute term meaning it is "fast".
The fastest turtle is not a fast 4 legged animal.
I challenge you to come up with a SQL solution that is faster.
Previous Topic: SQL query help (merged 2 cross-posts)
Next Topic: Trigger Problem,Please help me out
Goto Forum:
  


Current Time: Sun Dec 04 08:54:55 CST 2016

Total time taken to generate the page: 0.07606 seconds