Interested to Buy Any Domain ? << Click Here >> for more details...
How can we manage the gap in a primary key column created
by a sequence?
Ex:a company has empno as primary key generated by a
sequence and some employees leaves in between.What is the
best way to manage this gap?
- 2 Answers
- 8452 Views
- Infosys, I also Faced
- E-Mail Answers
Answer Posted / harikanthkoneti
yes...yes........ you can do it simply by updating that
table using rownum yar. see my example below.
SQL> create table test(sno number(2),sname varchar2(20));
Table created.
now if you have the data in the table like follows.
SQL> select * from test;
SNO SNAME
---------- --------------------
1 sdf
3 sdf
6 ewr
7 rt
90 drt
now just do the following simple statement.
SQL> update test set sno=rownum;
5 rows updated.
now here is the o/p for eliminating the gap generated by the
sequence.
SQL> select * from test;
SNO SNAME
---------- --------------------
1 sdf
2 sdf
3 ewr
4 rt
5 drt
then do the commit. thats all....................
now enjoy with this coding
suppose if you want to allocate the with any kind of series
of values for updating the table just follow the below
procedure
SQL> update test set sno=sql.nextval;
NOTE:sql is the sequence.
this statement will updates ur table with
what ever you given with the start with value and it ll
increment by which value you had given in the increment by
clause.
| Is This Answer Correct ? | 1 Yes | 3 No |
Post New Answer View All Answers
What view(s) do you use to associate a users SQLPLUS session with his o/s process?
Explain what are the different type of segments?
Is primary key indexed by default in oracle?
How will you differentiate between varchar & varchar2?
What is the difference between post-database commit and post-form commit?
How can we view last record added to a table?
How to load data through external tables?
How remove data files before opening a database?
what is partitioning? Types of partitioning. explain? what is the new kind of partitioning type introduced in 9i?
What are a collation and state the different types of collation sensitivity?
What are transaction isolation levels supported by oracle?
If youre unsure in which script a sys or system-owned object is created, but you know its in a script from a specific directory, what UNIX command from that directory structure can you run to find your answer?
What are the types of partitions in oracle?
Difference between cartesian join and cross join?
How to filter out duplications in the returning rows using oracle?
