MySQL Question

SQL Query, how to select a interval where the next value is current value +1, identifying the next gap and leave everything else untouched?

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.

Edit: Found the answer thanks to Thorsten Kettner, my query ended up like this:

update pair set id = id + 1
where id >= 1
and id <
(
select min(id)
from (select * from pair) as gap
where gap.id > 1 and not exists
(
select *
from (select * from pair) as oneless
where oneless.id = gap.id - 1
)
)
order by id desc

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
  )
);