Home » SQL & PL/SQL » SQL & PL/SQL » auto increment (PLSQL)
auto increment [message #564050] Tue, 21 August 2012 01:58 Go to next message
vaibhav15211
Messages: 21
Registered: August 2012
Location: Hyderabad
Junior Member
I have a table named student_details with columns "NAME","ADDRESS","COURSE" with several rows of data all ready inserted
I have to add one more column "ID" which increments automatically.

I tried to do this using SEQUENCE but no values got inserted for already existing rows in "ID".
Please suggest how to write a script that automatically increments and inserts values for already existing rows also.
Re: auto increment [message #564057 is a reply to message #564050] Tue, 21 August 2012 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I tried to do this using SEQUENCE but no values got inserted for already existing rows in "ID".


Did you also create a trigger for this?

autoincrement

Regards
Michel

[Updated on: Tue, 21 August 2012 03:03]

Report message to a moderator

Re: auto increment [message #564059 is a reply to message #564057] Tue, 21 August 2012 03:06 Go to previous messageGo to next message
vaibhav15211
Messages: 21
Registered: August 2012
Location: Hyderabad
Junior Member
yes I created a before insert trigger but it didn't worked following is the code:

create sequence t1_seq
start with 1
increment by 1
nomaxvalue;

create trigger t1_trigger
before insert on student
for each row
begin
select t1_seq.nextval into :new.id from dual;
end;
Re: auto increment [message #564061 is a reply to message #564059] Tue, 21 August 2012 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have a table named student_details with columns

Quote:
create trigger t1_trigger before insert on student


You did not create the trigger on the correct table.

Please Use SQL*Plus and copy and paste your session, the WHOLE session including table, sequence, trigger creation and insert execution.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Re: auto increment [message #564066 is a reply to message #564061] Tue, 21 August 2012 03:19 Go to previous messageGo to next message
vaibhav15211
Messages: 21
Registered: August 2012
Location: Hyderabad
Junior Member
create sequence t1_seq
start with 1
increment by 1
nomaxvalue;

create trigger t1_trigger
before insert on student_details
for each row
begin
select t1_seq.nextval into :new.id from dual;
end;
Re: auto increment [message #564067 is a reply to message #564066] Tue, 21 August 2012 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post ALL xhat I asked.
And post it FORMATTED.

Regards
Michel
Re: auto increment [message #564077 is a reply to message #564050] Tue, 21 August 2012 05:23 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Hi,
You can try,

create table demo (username varchar2(50), user_id number); 


I have added 11 rows to it.

select * from demo;
USERNAME                          USER_ID
------------------------------ ----------
OUTLN                                   9
SYS                                     0
SYSTEM                                  5
GODBLR02A                              12
TEST_MAIL                              43
OBE_ADMIN                              13
APPQOSSYS                              33
EXFSYS                                 42
DBSNMP                                 32
DIP                                    16
ORACLE_OCM                             23


Now, Alter the table, add the ID column:

alter table demo add id_col number;


Run the update statement:

update demo set id_col=rownum;


Verify:
select * from demo;

USERNAME                          USER_ID     ID_COL
------------------------------ ---------- ----------
OUTLN                                   9          1
SYS                                     0          2
SYSTEM                                  5          3
GODBLR02A                              12          4
TEST_MAIL                              43          5
OBE_ADMIN                              13          6
APPQOSSYS                              33          7
EXFSYS                                 42          8
DBSNMP                                 32          9
DIP                                    16         10
ORACLE_OCM                             23         11



NOTE: I have tried it in DEV environment, Please don't use the above steps in PROD, Verify thrice before adapting.
I Have not tested the code properly.
Re: auto increment [message #564079 is a reply to message #564077] Tue, 21 August 2012 05:31 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Sorry Missed One part,

Once the above steps are completed, Create a sequence, starting from MAX(ID_COL) + 1 (Here Its 12).

create sequence demo_seq start with 12 increment by 1;


Regards,
Veeresh
Re: auto increment [message #564082 is a reply to message #564079] Tue, 21 August 2012 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT what is asked.
There is NO rows in the table, then rows are added and ID must be automatically filled (see the title).

I advice you to click on the first link I provided to know what is the question and the solution.

Regards
Michel
Re: auto increment [message #564083 is a reply to message #564082] Tue, 21 August 2012 06:06 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Hi Michel,

As per my understanding of the question, He has a table with several rows,

I have a table named student_details with columns "NAME","ADDRESS","COURSE" with several rows of data all ready inserted
I have to add one more column "ID" which increments automatically.


Regards,
Veeresh
Re: auto increment [message #564084 is a reply to message #564083] Tue, 21 August 2012 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, the key work is "which increments automatically" and the title also said it.
Follow the link I posted, this is a FAQ.
Or OP poorly described his issue and used not appropriate words (as they are defined in all the dbms that support autoincrement column).

Regards
Michel

[Updated on: Tue, 21 August 2012 06:12]

Report message to a moderator

Re: auto increment [message #564085 is a reply to message #564084] Tue, 21 August 2012 06:23 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Michel,

I went through the question(As You said in one of previous post), I just tried to help. I always wanted to share whatever I know, Hoping that someone would correct me if make any mistakes while helping. Always apologized whenever I went wrong/mislead.


But your reply was always harsh and it hurts.


Thank You.
Veeresh
Re: auto increment [message #564112 is a reply to message #564085] Tue, 21 August 2012 08:04 Go to previous message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, I apologize to have hurted you, it was not my intention.
I'd better say it in another way as OP's question is unclear.

Regards
Michel
Previous Topic: def : hierarchy query
Next Topic: how to create a directory on init.ora file?
Goto Forum:
  


Current Time: Sat Dec 20 19:29:10 CST 2014

Total time taken to generate the page: 0.08781 seconds