At 11:00 AM +0100 5/2/06, Ross wrote:
Just say I have a db
CREATE TABLE `mytable` (
`id` int(4) NOT NULL auto_increment,
`fileName` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
when I add items they go id 1,2,3 etc. Whn I delete them gaps appear. 1, 3,
7. I need to know
(a) when items are removed how can I sort the database to all the gaps are
take out 1, 3, 7 becomes 1,2,3
Ross:
I am sure that the advice of everyone here and on any mysql list will
be NOT to do what you are asking. You're looking at the ID as if the
order of that field means something, it shouldn't.
However, to provide you with what you asked for, please review:
/* Renumber the Existing Sequence
alter table mytable
drop id,
add id int unsigned not null auto_increment,
auto_increment = 1;
Keep in mind that by doing this, your dB should remain flat -- you
cannot successfully renumber a relational dB without also considering
all the dependant tables, which is not trivial.
HTH's more than it hurts.
tedd
--
--------------------------------------------------------------------------------
http://sperling.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
