Home » SQL & PL/SQL » SQL & PL/SQL » To do in a single query (Oracle 91)
To do in a single query [message #323391] Wed, 28 May 2008 06:37 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

I have a table ASDF with fields A1(number) and A2(varchar2(50)) .
Where A1 is a primary key values starting from 1 .
IF there r 10 records in that table if first 5 records r deleted then when
inserting records it want to start from 1 and then increment after each insert .
Can we do it in a sngle query

Please Help Me
Thanks in Advance
Re: To do in a single query [message #323397 is a reply to message #323391] Wed, 28 May 2008 06:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Not in a multi-user environment where you don't want performance to deteriorate massively as the number of users inserting into the table increases.
Re: To do in a single query [message #323575 is a reply to message #323391] Wed, 28 May 2008 21:17 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I love your answer J. Wish I had thought of it.

I like reading posts on OraFAQ. Shows me how I am deficient in my Oracle thinking habits. Been working with Oracle RDBMS for 25 years and still my first thoughts are not about concurrency and scalability. In spite of a vaste amount of Oracle erratta I keep struggling in many areas as a beginimg thinker.

My thanks to you for this reminder. One of the reasons I keep reading.

rakeshramm might we ask you: what is the application your are building that requires such a PK scheam?

Kevin
Re: To do in a single query [message #323602 is a reply to message #323391] Thu, 29 May 2008 00:12 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

No it is done in single user enviornment
Re: To do in a single query [message #323656 is a reply to message #323602] Thu, 29 May 2008 02:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you can guarantee that it is a single user environment, you can do something like this:
SQL> create table min_val_test (col_1 number, col_2 varchar2(10), constraint mvt_pk primary key(col_1));

Table created.

SQL> insert into min_val_test (select level+4, 'A' from dual connect by level <= 5);

5 rows created.

SQL> select * from min_val_test;

     COL_1 COL_2
---------- ----------
         5 A
         6 A
         7 A
         8 A
         9 A

SQL> create or replace function min_val return number as
  2    v_return  pls_integer;
  3    v_max     pls_integer;
  4    v_count   pls_integer;
  5  begin
  6    select count(*),max(col_1)
  7    into   v_count,v_max
  8    from   min_val_test;
  9  
 10    if v_count = v_max then -- all values taken
 11      return v_max + 1;
 12    else
 13      select min(rnum)
 14      into  v_return
 15      from (select col_1
 16                  ,rownum rnum
 17            from (select col_1 
 18                  from min_val_test 
 19                  order by col_1)
 20            )
 21      where rnum < col_1;           
 22    
 23    end if;
 24    return v_return;
 25  end;
 26  /

Function created.

SQL> begin
  2    for i in 1..10 loop
  3      insert into min_val_Test (col_1,col_2) values (min_val,'B-'||i);
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from min_val_test order by col_1;

     COL_1 COL_2
---------- ----------
         1 B-1
         2 B-2
         3 B-3
         4 B-4
         5 A
         6 A
         7 A
         8 A
         9 A
        10 B-5
        11 B-6
        12 B-7
        13 B-8
        14 B-9
        15 B-10

15 rows selected.

SQL> 

I've not yet managed to create a version that will allow you to insert multiple rows at once - you hit Mutating Table errors (and the fact that you could get those without using a trigger was an learning experience for me)

Kevin: Thanks for the compliment. It's always nice to be appreciated.
Re: To do in a single query [message #323711 is a reply to message #323391] Thu, 29 May 2008 05:26 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member



Thanks Jrowbottom for your help . I have a query can i do this in a single query

Thanks
Re: To do in a single query [message #323719 is a reply to message #323711] Thu, 29 May 2008 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table min_val_test (col_1 number, col_2 varchar2(10), constraint mvt_pk primary key(col_1));

Table created.

SQL> insert into min_val_test (select level+4, 'A' from dual connect by level <= 5);

5 rows created.

SQL> select * from min_val_test order by col_1;
     COL_1 COL_2
---------- ----------
         5 A
         6 A
         7 A
         8 A
         9 A

5 rows selected.

SQL> Def nb_to_insert=10
SQL> insert into min_val_test (col_1, col_2)
  2  with 
  3    all_numbers as (
  4      select level num 
  5      from dual 
  6      connect by level <= (select max(col_1)+&nb_to_insert from min_val_test)
  7    ),
  8    available_numbers as (
  9      select num, rownum line
 10      from ( select num from all_numbers
 11             minus 
 12             select col_1 from min_val_test
 13             order by 1 )
 14      order by num
 15    )
 16  select num, 'B-'||rownum
 17  from available_numbers
 18  where rownum <= &nb_to_insert
 19  /

10 rows created.

SQL> select * from min_val_test order by col_1;
     COL_1 COL_2
---------- ----------
         1 B-1
         2 B-2
         3 B-3
         4 B-4
         5 A
         6 A
         7 A
         8 A
         9 A
        10 B-5
        11 B-6
        12 B-7
        13 B-8
        14 B-9
        15 B-10

15 rows selected.

Regards
Michel
Re: To do in a single query [message #323754 is a reply to message #323719] Thu, 29 May 2008 06:32 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Very nice.
Shocked
Previous Topic: is this possible?
Next Topic: table redefinition
Goto Forum:
  


Current Time: Mon Dec 05 06:57:34 CST 2016

Total time taken to generate the page: 0.24386 seconds