Re: sort of defragmentation?

[Christian Mack]

Mike Otto wrote:
> 
> Hi!
> 
> I'm using  MySQL 3.22.14b-gamma on a Linux Box.
> 
> Lets say I have this little table:
> 
> ID     Article
> 
> 1      strawberrys
> 2      lemons
> 3      apples
> 4      flowers
> 
> After: Delete * from table where ID='2'
> the table looks like this
> 
> ID    Article
> 
> 1     strawberrys
> 3     apples
> 4     flowers
> 
> In this table I'm missing the ID=2
> 
> So here is my question:
> 
> I would like to run a Query (or whatever) to make
> the table look like this:
> 
> 1   strawberrys
> 2   apples
> 3   flowers
> 
> (sorted order, without missing IDs)
> 
> Does somebody know how to do it?
> Did I oversee something in the manual?
> 
> Thanks in advance and BTW: Many thanks to Monty and
> all the others in this mailinglist teaching me everyday
> how to use MySQL.
> 
> Mike
> 
> --
> Mike Otto - mikeqdai@stripped

Hi Mike

You can't do that with one query.
But try this:

LOCK TABLE fruits WRITE
DELETE FROM fruits WHERE ID=2
UPDATE fruits SET ID=ID-1 WHERE ID>2
UNLOCK TABLE

This obviously only works, if you delete only one row.
And you have to watch out for dependencies on ID in other tables.

Tschau
Christian