Home » SQL & PL/SQL » SQL & PL/SQL » creating a gap in rec_nos (Oracle 10g)
creating a gap in rec_nos [message #420956] Thu, 03 September 2009 08:38 Go to next message
cygnusx04
Messages: 16
Registered: February 2008
Junior Member
this should be so easy but I just can't get my head round it.

I have a series of REC_NO values, I need to increase a set number so I can make a gap to insert new values. i.e

101
102
103
104
105
106

becomes

101
107
108
109
...........


create table test (rec_no INTEGER, table_no INTEGER, CONSTRAINT test1 UNIQUE (rec_no));

insert into test values (101,999);
insert into test values (102,999);
insert into test values (103,999);
insert into test values (104,999);
insert into test values (105,999);
insert into test values (106,999);

SQL> update test set rec_no=(select max(rec_no)+1 from test) where table_no=999 and rec_no>101;

update test set rec_no=(select max(rec_no)+1 from test) where table_no=999 and rec_no>101;
*
ERROR at line 1:
ORA-00001: unique constraint (TEST1) violated


I though the AUTOCOMMIT option might help - alas no.
Re: creating a gap in rec_nos [message #420958 is a reply to message #420956] Thu, 03 September 2009 08:44 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
It's actually simpler than the query you tried:

SQL> update test set rec_no= rec_no + 1 where table_no=999 and rec_no>101;

5 rows updated.

SQL>


Gives you a gap of one.

[Updated on: Thu, 03 September 2009 08:44]

Report message to a moderator

Re: creating a gap in rec_nos [message #420959 is a reply to message #420956] Thu, 03 September 2009 08:45 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
NOpe, Totally wrong, really need to stop snap answers when I'm busy Sad

[Updated on: Thu, 03 September 2009 08:46]

Report message to a moderator

Re: creating a gap in rec_nos [message #420960 is a reply to message #420956] Thu, 03 September 2009 08:48 Go to previous messageGo to next message
cygnusx04
Messages: 16
Registered: February 2008
Junior Member
sorry that is no good. that would trounce over existing rec_no's ..... the new rec_no's need to be new ones .. i.e higher than anything else currently allocated. Hence the max(rec_no) bit.
Re: creating a gap in rec_nos [message #420964 is a reply to message #420960] Thu, 03 September 2009 08:52 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
But won't you trounce over them then anyway, when you insert the new ones?

You could to it in two steps: First figure out what value you have to add to the rec_nos, then add that value.
Re: creating a gap in rec_nos [message #420969 is a reply to message #420956] Thu, 03 September 2009 08:56 Go to previous messageGo to next message
cygnusx04
Messages: 16
Registered: February 2008
Junior Member
101
102
103
104
105
106

becomes

101
107
108
109

so I move 102 ->107, 103 ->108, 104->109 .....

so these are new numbers and I'm not trouncing anything. Because I have now moved these I don't mind reusing those rec_nos (they are now free).

I can do this manually with many update statements, but thought it really must be easy with a simple update statement.
Re: creating a gap in rec_nos [message #420970 is a reply to message #420969] Thu, 03 September 2009 09:00 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can do it with one update statement, once you have figured out that you have to add "5" to move "102" to "107"

update test set rec_no= rec_no + 5 where table_no=999 and rec_no>101;
Re: creating a gap in rec_nos [message #420972 is a reply to message #420970] Thu, 03 September 2009 09:04 Go to previous messageGo to next message
cygnusx04
Messages: 16
Registered: February 2008
Junior Member
my example table is very much simplified.... I will be running this script on many different systems with different rec_no ranges ... the whole idea of the script is for simplicity and reducing manual involvement.
Re: creating a gap in rec_nos [message #420973 is a reply to message #420972] Thu, 03 September 2009 09:08 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, this would to it automatically for the test case, as long as you can make sure that nobody else is working with the table.

DECLARE 
  v_gap_at   NUMBER := 101;
  v_increase NUMBER;
BEGIN
  select max(rec_no) - v_gap_at INTO v_increase FROM test;
  update test set rec_no= rec_no + v_increase where table_no=999 and rec_no> v_gap_at;
END;
/


It might also be worth to take a step back, and analyse again what the actual requirement/business need is. It is generally not a good idea to mess with "record ids" on a production system.
Re: creating a gap in rec_nos [message #420974 is a reply to message #420956] Thu, 03 September 2009 09:10 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Generally you need to increment the id by the difference between the max value and min value that's greater than the ones you want to leave plus one.

So this:
UPDATE test 
SET rec_no = rec_no + (SELECT max(rec_no) - min(rec_no) + 1 
                       FROM test 
                       WHERE table_no=999 AND rec_no>101) 
WHERE table_no=999 AND rec_no > 101;


But I would question the design of any system that wants to rearrange ids like this.
Re: creating a gap in rec_nos [message #420975 is a reply to message #420956] Thu, 03 September 2009 09:16 Go to previous message
cygnusx04
Messages: 16
Registered: February 2008
Junior Member
thankyou
Previous Topic: A Tricky Left-Outer Join :-
Next Topic: REF CURSOR with NVARCHAR2 data type
Goto Forum:
  


Current Time: Sun Dec 11 02:19:06 CST 2016

Total time taken to generate the page: 0.04456 seconds