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 Otto | 11 Mar |
• Re: sort of defragmentation? | kalle volkov | 11 Mar |
• Re: sort of defragmentation? | Christian Mack | 11 Mar |
• Re: sort of defragmentation? | Charles Kirby | 11 Mar |
• Re: sort of defragmentation? | Sasha Pachev | 11 Mar |
• Re: sort of defragmentation? | Charles Kirby | 11 Mar |
• Re: sort of defragmentation? | Sasha Pachev | 11 Mar |
• Re: sort of defragmentation? | Charles Kirby | 11 Mar |
• Splitting query results among machines? | Brian Bray | 11 Mar |
• RE: Splitting query results among machines? | Brett Error | 11 Mar |
• SIGSEV using Mysql++ | Sander Pilon | 26 Aug |
• Re: sort of defragmentation? | Ed Carp | 11 Mar |
• Re: sort of defragmentation? | Sasha Pachev | 11 Mar |
• Re: sort of defragmentation? | Ed Carp | 11 Mar |
• Re: sort of defragmentation? | Christian Mack | 12 Mar |
• Re: Splitting query results among machines? | Fred Lindberg | 11 Mar |
• Re: sort of defragmentation? | Ed Carp | 12 Mar |
• Re: sort of defragmentation? | Christian Mack | 13 Mar |
• Re: sort of defragmentation? | Ed Carp | 13 Mar |