The other day I had someone ask me how to automatically assign unique numbers (integers) to a column in an Oracle database. I took an Oracle SQL class a few years ago and I remember learning about the “create sequence” command. After reviewing a couple archived homework assignments for syntax, here are three typical examples of its use.
create sequence myblog_ID increment by 1 start with 100;
insert into MYBLOG (Blog, Author, ID) values (‘My SysAd Blog’,’esofthub’,myblog_ID.Nextval);
create sequence topblog_ID increment by 1 start with 1000;
insert into TOPBLOG (Blog, Author, ID) values (‘TopBlogLists’,’esofthub’,topblog_ID.Nextval);
create sequence mysysad_ID increment by 1 start with 10000;
insert into MYSYSAD (Blog, Author, ID) values (‘My SysAd Blog’,’esofthub’,mysysad_ID.Nextval);
1 comment:
I ran into a very similar issue in MySQL the other day. The MySQL equivalent is:
SET @counter = -1;
UPDATE MyTable SET MyColumn=(@counter:=(@counter+1))
Note that you initialize @counter to -1 so that it ends up at zero for the first record, because the := operator returns the assigned value. If you want to 1 index instead of zero index, start @counter at 0.
Post a Comment