Home » SQL & PL/SQL » SQL & PL/SQL » Adding a New Column to an existing non empty table
Adding a New Column to an existing non empty table [message #218931] Mon, 12 February 2007 02:45 Go to next message
Rana M
Messages: 4
Registered: February 2007
Junior Member
Hi
I need you help??

I have a table Sectionstudents_tbl which contains 2 columns sectionid and studentid the combination of those 2 columns make table primary key, I want to add new column sectionstudentid autoincremet column that indicates the primary key instead of the other one for this table,the problem is that table contains 2183 rows.
So How to do this in SQL Plus??????

Thanks in advance
Re: Adding a New Column to an existing non empty table [message #218939 is a reply to message #218931] Mon, 12 February 2007 03:10 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


create a sequence,create a new table with sectionstudentid as primary key ,insert the data from your old table to this new table and then drop the old table.


regards,
Re: Adding a New Column to an existing non empty table [message #219066 is a reply to message #218931] Mon, 12 February 2007 16:16 Go to previous messageGo to next message
pselvam76
Messages: 12
Registered: February 2007
Junior Member
Hi

See if this works out for you.

create table Sectionstudents_tbl_new as
select rownum as sectionstudentid, s.* from
sectionstudents_tbl s;

drop table Sectionstudents_tbl;

rename Sectionstudents_tbl_new to Sectionstudents_tbl;

alter table Sectionstudents_tbl add
(constraint pk_secstudentid primary key(sectionstudentid));

Thanks
Panneer Selvam
Re: Adding a New Column to an existing non empty table [message #219114 is a reply to message #219066] Tue, 13 February 2007 00:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you both insist on recreating the table. There are only 2183 rows in the original one!
Just add the id column and update it. Should be a matter of seconds.
Re: Adding a New Column to an existing non empty table [message #219138 is a reply to message #219114] Tue, 13 February 2007 02:46 Go to previous messageGo to next message
Rana M
Messages: 4
Registered: February 2007
Junior Member
If I copy data to a new table then I delete all data from the original one and add id column then copy data back to the original one I have constraints in this table so may be they will be lost??
Re: Adding a New Column to an existing non empty table [message #219164 is a reply to message #219138] Tue, 13 February 2007 08:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
exactly my point.
Creating a new table requires you to recreate every constraint and index as well.
Overkill for a 2000-row table.
Re: Adding a New Column to an existing non empty table [message #219254 is a reply to message #218931] Tue, 13 February 2007 12:38 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
CREATE SEQUENCE MY_TABLE_SEQ
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
NOCYCLE
CACHE 20
NOORDER;

ALTER TABLE MY_TABLE ADD NEW_COLUMN NUMBER(15);

UPDATE MY_TABLE
SET NEW_COLUMN = MY_TABLE_SEQ.NEXTVAL;

drop the primary key and rebuild it using new_column.

Your new column is setup and populated. There is NO reason to make another table.

[Updated on: Tue, 13 February 2007 12:41]

Report message to a moderator

Re: Adding a New Column to an existing non empty table [message #219441 is a reply to message #219254] Wed, 14 February 2007 07:32 Go to previous messageGo to next message
Rana M
Messages: 4
Registered: February 2007
Junior Member
Its Done Thaaaaaaaaaaaaaaaaaaaaaaaanks alooooooooooooot
Re: Adding a New Column to an existing non empty table [message #219972 is a reply to message #219441] Sat, 17 February 2007 01:11 Go to previous messageGo to next message
Rana M
Messages: 4
Registered: February 2007
Junior Member
Hi
How about if I drop sequence after updating Does it make a problem??
Re: Adding a New Column to an existing non empty table [message #219976 is a reply to message #219972] Sat, 17 February 2007 01:33 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

 what happens when you drop the column ?




regards,
Re: Adding a New Column to an existing non empty table [message #219977 is a reply to message #219972] Sat, 17 February 2007 02:34 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Rana M wrote on Sat, 17 February 2007 08:11
Hi
How about if I drop sequence after updating Does it make a problem??

The sequence is not 'bound' to the column, so if you used the sequence for filling initial values only, you can drop it safely.
But how are you going to fill the id column in the future?
Previous Topic: How to Import & Export Data in Excel sheet???Using oracle 8i&9i???
Next Topic: How we will call another procedure within procedure??
Goto Forum:
  


Current Time: Fri Dec 09 06:15:42 CST 2016

Total time taken to generate the page: 0.22157 seconds