Clear a table in MySQL that uses auto-increment

Question

My table looks like this:

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

table_id uses the AUTO_INCREMENT option. (these values ​​came due to a strange error in my program ... don't ask :-))

I want to clear it with the following result:

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

Is it possible?

And is there a way to clear this automatically through cronjob or something else?

---

Decision:

Gordons' 1st answer solution worked correctly. But I needed to add code because auto_increment did not want to automatically reset. Final decision:

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 counter used this solution)

Answer 1

Try the following:

set @rn := 0;

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

, truncate-and-reload:

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;

Answer 2

: 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 

• - SQL Fiddle

Answer 3

(InnoDB, MyISAM...) , (max id + 1), , , , auto_increment, ( 235).

, :

ALTER TABLE tbl AUTO_INCREMENT = 7;

( 7, 6 , ).