List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:March 11 1999 2:35pm
Subject:Re: sort of defragmentation?
View as plain text  
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

Thread
sort of defragmentation?Mike Otto11 Mar
  • Re: sort of defragmentation?kalle volkov11 Mar
  • Re: sort of defragmentation?Christian Mack11 Mar
    • Re: sort of defragmentation?Charles Kirby11 Mar
      • Re: sort of defragmentation?Sasha Pachev11 Mar
        • Re: sort of defragmentation?Charles Kirby11 Mar
          • Re: sort of defragmentation?Sasha Pachev11 Mar
            • Re: sort of defragmentation?Charles Kirby11 Mar
              • Splitting query results among machines?Brian Bray11 Mar
                • RE: Splitting query results among machines?Brett Error11 Mar
  • SIGSEV using Mysql++Sander Pilon26 Aug
Re: sort of defragmentation?Ed Carp11 Mar
  • Re: sort of defragmentation?Sasha Pachev11 Mar
Re: sort of defragmentation?Ed Carp11 Mar
  • Re: sort of defragmentation?Christian Mack12 Mar
Re: Splitting query results among machines?Fred Lindberg11 Mar
Re: sort of defragmentation?Ed Carp12 Mar
Re: sort of defragmentation?Christian Mack13 Mar
Re: sort of defragmentation?Ed Carp13 Mar