Home » SQL & PL/SQL » SQL & PL/SQL » update column
update column [message #254312] Thu, 26 July 2007 07:16 Go to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

hi experts


i have a table with 1025453 records. it has 4 columns in it. one column is empty and i want to update whis empty feiuld with series of number like '0000000001' to '0001025453'

how can i do that .. please help me out.



regards

Anwer
Re: update column [message #254315 is a reply to message #254312] Thu, 26 July 2007 07:22 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One way would be using a sequence, and another might be using the ROWNUM. Something like this:
CREATE SEQUENCE my_seq;

UPDATE TEST SET
  id = LPAD(my_seq.NEXTVAL, 7, '0');
or
UPDATE TEST SET
  id = LPAD(ROWNUM, 7, '0');
Re: update column [message #254316 is a reply to message #254312] Thu, 26 July 2007 07:22 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
SQL> CREATE TABLE mhe_foo( col1 NUMBER
  2                      , col2 VARCHAR2(30)
  3                      )
  4  /

Table created.

SQL> INSERT INTO mhe_foo(col2)
  2  SELECT table_name
  3  FROM   all_tables
  4  WHERE  owner = 'HR'
  5  /

7 rows created.

SQL>
SQL> SELECT * FROM mhe_foo
  2  /

      COL1 COL2
---------- ------------------------------
           REGIONS
           LOCATIONS
           DEPARTMENTS
           JOBS
           EMPLOYEES
           JOB_HISTORY
           COUNTRIES

7 rows selected.

SQL>
SQL> --
SQL> -- This is what you are after:
SQL> --
SQL> UPDATE mhe_foo
  2  SET col1 = ROWNUM
  3  /

7 rows updated.

SQL>
SQL> SELECT * FROM mhe_foo
  2  /

      COL1 COL2
---------- ------------------------------
         1 REGIONS
         2 LOCATIONS
         3 DEPARTMENTS
         4 JOBS
         5 EMPLOYEES
         6 JOB_HISTORY
         7 COUNTRIES

7 rows selected.

SQL>
SQL> DROP TABLE mhe_foo
  2  /

Table dropped.


MHE
Re: update column [message #254318 is a reply to message #254312] Thu, 26 July 2007 07:29 Go to previous message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

thanks

experts
Previous Topic: Oracle 7.3 PL/SQL
Next Topic: Need help for locks related issue
Goto Forum:
  


Current Time: Sun Dec 11 00:23:32 CST 2016

Total time taken to generate the page: 0.23334 seconds