| Adding a New Column to an existing non empty table [message #218931] |
Mon, 12 February 2007 02:45  |
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 #219066 is a reply to message #218931] |
Mon, 12 February 2007 16:16  |
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 #219254 is a reply to message #218931] |
Tue, 13 February 2007 12:38 |
Bill B
Messages: 1971
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 #219977 is a reply to message #219972] |
Sat, 17 February 2007 02:34 |
Frank
Messages: 7901
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
