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
