SQL Question

SQL Query, how to select a number interval where the next value is current value +1?

I'm making a program on java using sql querys and i'd like to update a sequence of ids adding 1 to each, but only in the sequence interval.

Let's say i have a table like this:


1 - a | 2 - b | 3 - c | 5 - e | 6 - f


And i'd like to "push" a's ID and subsequents forward by 1, but only while the difference between one another is still 1. The result would be like this:


2 - a | 3 - b | 4 - c | 5 - e | 6 - f


I know it is possible to do programatically, but is there a way to do this with SQL querys? If not, what would be the best way to do this?

Thanks in advance.

Answer

If you know where the gap is that you want closed (ID 4 in your case) use this:

update mytable set id = id + 1 where id < 4;

If you don't know where the gap is, find it:

select min(id) - 1
from mytable
where not exists
(
  select * 
  from mytable oneless
  where oneless.id = mytable.id - 1
);

We can combine both statments now to do the update:

update mytable set id = id + 1 
where id <
(
  select min(id) - 1
  from mytable
  where not exists
  (
    select * 
    from mytable oneless
    where oneless.id = mytable.id - 1
  )
);