Home » SQL & PL/SQL » SQL & PL/SQL » updating values of a column (oracle 10g,win xp)
updating values of a column [message #430059] Sat, 07 November 2009 21:06 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi all

i have a table mobile_data where i have a column mobile_number

the values in the column are like

Quote:
44173203454
44173203445
44173205573
44173205857
44173484058


I need to replace 44 by 0 for all the values.Hence can you please let me know how to update the values.

Also i need to add a date field int he table and populate the date field values as 15-oct-2009

Thanks and Regards
Swastik
Re: updating values of a column [message #430065 is a reply to message #430059] Sat, 07 November 2009 22:52 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Check REPLACE for replacing the 44's...I assume you want ALL occurrences of a 44 replaced ?

For modifying the table, check ALTER TABLE.

Re: updating values of a column [message #430067 is a reply to message #430059] Sat, 07 November 2009 22:55 Go to previous message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
swas_fly wrote on Sun, 08 November 2009 04:06
I need to replace 44 by 0 for all the values.Hence can you please let me know how to update the values.

What about using UPDATE statement? Just write the logic into SQL. I also suppose you are talking about replacing starting '44', not all their occurrances (so number '0123446789' shall not be changed to '012306789'). If my assumption is wrong, change the following logic appropriately.
The new value will be '0' followed by the rest of number (from the third character):
SET mobile_number = '0'||SUBSTR( mobile_number, 3 )

You will update numbers which first two characters are '44':
WHERE SUBSTR( mobile_number, 1, 2 ) = '44'

swas_fly wrote on Sun, 08 November 2009 04:06
Also i need to add a date field int he table and populate the date field values as 15-oct-2009

Use ALTER TABLE command, then UPDATE. It may take long for large table though.
List of all SQL commands and functions with their description and examples is available in SQL Reference book. It is part of Oracle documentation, available e.g. online on http://tahiti.oracle.com/.
Previous Topic: forming insert value dynamically based on columns in destination
Next Topic: Print command blank line (merged)
Goto Forum:
  


Current Time: Mon Sep 26 04:19:30 CDT 2016

Total time taken to generate the page: 0.09628 seconds