Home » SQL & PL/SQL » SQL & PL/SQL » How to add a sequence column to an existing table with records (Oracle SQL Developer: ODMiner 3.0.04)
How to add a sequence column to an existing table with records [message #572252] Sat, 08 December 2012 17:31 Go to next message
Saya22
Messages: 14
Registered: December 2012
Location: London
Junior Member
Hello,

I had created a new table named USERLOG with two fields from a previous VIEW. The table already consist of about 9000 records. The two fields taken from the VIEW, i.e. weblog_views consist of IP (consists of IP address), and WEB_LINK (consists of URL). This is the code I used,

CREATE TABLE USERLOG
AS
SELECT C_IP, WEB_LINK FROM weblog_views;


I want to add another column to this table called the USER_ID, which would consists of a sequence starting with 1 to 9000 records to create a unique id for each existing rows. I need help with this part. I'm using Oracle SQL Developer: ODMiner version 3.0.04.
I tried using the AUTO-INCREMENT option,

ALTER TABLE USERLOG
ADD USER_ID INT UNSIGNED NOT NULL AUTO_INCREMENT;


But I get an error with this,

Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 -  "invalid ALTER TABLE option"


So I would really appreciate any help that I can get!
Re: How to add a sequence column to an existing table with records [message #572253 is a reply to message #572252] Sat, 08 December 2012 18:12 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
Pretty sure auto_increment is not valid Oracle syntax.
Re: How to add a sequence column to an existing table with records [message #572254 is a reply to message #572253] Sat, 08 December 2012 18:14 Go to previous messageGo to next message
Saya22
Messages: 14
Registered: December 2012
Location: London
Junior Member
What else could I use?
Re: How to add a sequence column to an existing table with records [message #572255 is a reply to message #572252] Sat, 08 December 2012 18:28 Go to previous messageGo to next message
BlackSwan
Messages: 22539
Registered: January 2009
Senior Member
Oracle has no AUTO_INCREMENT feature

You need a SEQUENCE & possibly a TRIGGER.
Re: How to add a sequence column to an existing table with records [message #572262 is a reply to message #572254] Sun, 09 December 2012 00:58 Go to previous message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This i a FAQ, just Google for "Oracle autoincrement" and you will have complete solutions.
Or read our {{AutoNumber and Identity columns]] wiki page.

Regards
Michel
Previous Topic: OPENING AND CLOSING BALANCE
Next Topic: Subquery SQL
Goto Forum:
  


Current Time: Wed Jul 30 19:51:50 CDT 2014

Total time taken to generate the page: 0.12608 seconds