List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 28 1999 9:46pm
Subject:Re: Index clearing?
View as plain text  
At 4:50 PM -0400 10/28/99, Bob Kline wrote:
>On Thu, 28 Oct 1999 robert@stripped wrote:
>
>>
>>  I created a rather involved set of tables in a DB. I did testing and
>>  now would like to reset the indexes and let it go live. I want the
>>  first record to be record 1 since I deleted alot of test records.
>>
>>  Anyone know how to reset the indexes without rebuilding the
>>  database?
>
>If you really mean "indexes" instead of the AUTO_INCREMENT columns, then
>the phrase "want the first record to be record 1" doesn't make any
>sense, since indexes in an RDBMS don't support the concept of a record
>number.
>
>If you really mean the AUTO_INCREMENT columns instead of "indexes," then
>for each table with such a column you want to create a temporary table
>with all the same columns except that column and select the data into
>this temporary table.  Then clear the original table and select the data
>back in, explicitly naming all columns except the AUTO_INCREMENT column.
>
>For example, in a table with columns a, b, and c, of which a is the
>AUTO_INCREMENT column:
>
>     CREATE TABLE tmp(b VARCHAR(30), c FLOAT);
>     INSERT INTO tmp SELECT b, c FROM original;
>     DELETE FROM original;
>     INSERT INTO original (b, c) SELECT b, c FROM tmp;
>     DROP TABLE tmp;
>
>I believe you would have found this same answer by looking in the
>archives for this mailing list.


It might be easier to use ALTER TABLE to drop the column and then
add it again.

-- 
Paul DuBois, paul@stripped
Thread
Index clearing?robert28 Oct
  • Re: Index clearing?Bob Kline28 Oct
    • Re: Index clearing?Paul DuBois28 Oct
      • Re: Index clearing?Bob Kline29 Oct