CODEWITHSUNDEEP
mysqlsqldatabase
GePu
GePu

Reputation: 320

clean up table in MySQL which uses auto increment

My table looks like this:

table_id | letters
-------- | -------
     4   |    a
    10   |    b
    24   |    c
    78   |    d
   110   |    e
   234   |    f

table_id uses the option AUTO_INCREMENT. (those values came because of a weird bug in my program ... don't ask :-) )

I want to clean it up with the following result:

table_id | letters
-------- | -------
    1    |    a
    2    |    b
    3    |    c
    4    |    d
    5    |    e
    6    |    f

Is that possible?

And is there a way to clean this automatically up via cronjob or something?

Solution:

The 1st solution of Gordons answer did work properly. But i needed to add some code because the auto_increment didn't want to reset automatically. The final solution is:

SET @rn := 0;

UPDATE t 
SET 
    table_id = (@rn:=@rn + 1)
ORDER BY table_id;

SELECT 
    COUNT(*)
INTO @AutoInc FROM
    t;

SET @s:=CONCAT('ALTER TABLE t AUTO_INCREMENT=', @AutoInc + 1);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

(to reset the counter i used this solution)

Upvotes: 4

Views: 1109

Answers (3)

bobflux
bobflux

Reputation: 11591

As a side note, and depending on table type (InnoDB, MyISAM...) please note that once you have cleaned up your table, the next inserted row may use (max id+1) as you probably expect, or it may use the auto_increment value that would have been used from the last insert (in your case it would be 235).

If this is the case, use:

ALTER TABLE tbl AUTO_INCREMENT = 7;

(I'm using 7 since your example has 6 lines, adjust accordingly).

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271121

Try doing the following:

set @rn := 0;

update t
    set table_id = (@rn := @rn + 1)
    order by table_id;

If that doesn't work, you can use the truncate-and-reload trick:

create table temp_t as
    select t.*
    from t;

truncate table t;

insert into t(letters)
    select letters
    from temp_t
    order by table_id;

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

Here is one way using Self Join

SELECT Count(*) table_id, 
       a.letters
FROM   Yourtable a 
       JOIN Yourtable b 
         ON a.table_id >= b.table_id 
GROUP  BY a.table_id, 
          a.letters

Upvotes: 0

Related Questions