...

View Full Version : Resolved Condense a table



Celestial
10-06-2011, 03:48 AM
Hi.
Is there any way to condense down the primary key of a table if there are missing numbers?
For example, the primary key is a number from 1 to 100 and it has random numbers missing from rows being deleted.
ID DATA
1 codingforums.com
2 codingforums.com
7 codingforums.com
9 codingforums.com

So it would end up reading:
ID DATA
1 codingforums.com
2 codingforums.com
3 codingforums.com
4 codingforums.com

Old Pedant
10-06-2011, 05:32 AM
CREATE TEMPORARY TABLE clone LIKE mytable;

INSERT INTO clone
SELECT * FROM mytable;

TRUNCATE mytable;

INSERT INTO mytable ( all, fields, except, auto, increment, field )
SELECT all, fields, except, auto, increment, field
FROM clone
ORDER BY auto_increment_field;

DROP TEMPORARY TABLE clone;

Old Pedant
10-06-2011, 05:33 AM
The difficulty with that, if it's not obvious, is that it is slow and clumsy and you better not be making any changes to the table in some other thread while this is going on.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum