OraFAQ Forum: SQL & PL/SQL » auto increment

Home » SQL & PL/SQL » SQL & PL/SQL » auto increment (PLSQL)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

auto increment [message #564050]

Tue, 21 August 2012 01:58

vaibhav15211
Messages: 13
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.

Report message to a moderator

Re: auto increment [message #564057 is a reply to message #564050]

Tue, 21 August 2012 03:01

Michel Cadot
Messages: 54205
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

vaibhav15211
Messages: 13
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;

Report message to a moderator

Re: auto increment [message #564061 is a reply to message #564059]

Tue, 21 August 2012 03:11

Michel Cadot
Messages: 54205
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

Report message to a moderator

Re: auto increment [message #564066 is a reply to message #564061]

Tue, 21 August 2012 03:19

vaibhav15211
Messages: 13
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;

Report message to a moderator

Re: auto increment [message #564067 is a reply to message #564066]

Tue, 21 August 2012 03:36

Michel Cadot
Messages: 54205
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Post ALL xhat I asked.
And post it FORMATTED.

Regards
Michel

Report message to a moderator

Re: auto increment [message #564077 is a reply to message #564050]

Tue, 21 August 2012 05:23

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.

Report message to a moderator

Re: auto increment [message #564079 is a reply to message #564077]

Tue, 21 August 2012 05:31

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

Report message to a moderator

Re: auto increment [message #564082 is a reply to message #564079]

Tue, 21 August 2012 05:48

Michel Cadot
Messages: 54205
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

Report message to a moderator

Re: auto increment [message #564083 is a reply to message #564082]

Tue, 21 August 2012 06:06

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

Report message to a moderator

Re: auto increment [message #564084 is a reply to message #564083]

Tue, 21 August 2012 06:10

Michel Cadot
Messages: 54205
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

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

Report message to a moderator

Re: auto increment [message #564112 is a reply to message #564085]

Tue, 21 August 2012 08:04

Michel Cadot
Messages: 54205
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 23 07:04:08 CDT 2013

Total time taken to generate the page: 0.09119 seconds