Mar 13, 2009

Insertion with condition

If there is a table with fields rt (number) which is a primary key and DF varchar2.If there r 10 records and first 5 records r deleted then when inserting records it want to start from 1 and then increment after each insert .

create table DF(DF VARCHAR2(3000),RT NUMBER(2) not null);

alter table DF add constraint E primary key (RT);

insert into df(rt,df)
with
all_numbers as (select level num from dual connect by level <=99),
available_number as (select num,rownum line from
(select num from all_numbers minus select rt from df order by 1) order by num )
select num,'B-'||rownum from available_number where rownum <=10;

----------------------------------------------------------------------

No comments: