codeigniter - How to reset auto increment on filled table?

Question

I'm using MySQL. I've tried this:

SET @num := 0; UPDATE rancangan_bulanan SET id = @num := (@num+1);

But using $this->db->query( "SET @num := 0; UPDATE rancangan_bulanan SET id = @num := (@num+1); doesn't give desired result.

It shows:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE rancangan_bulanan SET id = @num := (@num+1)' at line 1

SET @num := 0; UPDATE rancangan_bulanan SET id = @num := (@num+1);

Filename: C:/xampp/htdocs/belajar/rkape/system/database/DB_driver.php

Line Number: 691

Anyone can help me?

My table will has a lot of deleted row, I just want to refresh the id. — Ukasha, May 22 '17 at 15:12
So lets say the table has IDs 1,5,7,8,9,10.... Resetting the auto increment to 1; will cause the database to try and use 1 again. is that really what you want? Gaps in autonumbers are FINE. keep in mind the whole point is simply to UNIQUELY identify the record. The ID has no knowledge of the record it represents nor should it (that's the point of a surrogate key). The fact that you once had a 2,3,4 is good to know. the fact that you want re-use 2,3,4 could be RISKY; especially if you have that key (ID) stored elsewhere for "historical" purposes. — xQbert, May 22 '17 at 15:30

score 2 · Accepted Answer · answered May 22 '17 at 15:26

I think that you missed ALTER TABLE your_table AUTO_INCREMENT =1;

SET  @num := 0; 

UPDATE rancangan_bulanan SET id = @num := (@num+1); 

ALTER TABLE rancangan_bulanan AUTO_INCREMENT =1;

Or if you want to reset the counter to 1, you can use:

ALTER TABLE rancangan_bulanan AUTO_INCREMENT = 1

Check this answer: How to reset AUTO_INCREMENT in MySQL?

If what you want is reassing the IDs in all rows you can use:

ALTER TABLE rancangan_bulanan DROP id

ALTER TABLE rancangan_bulanan ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id), AUTO_INCREMENT=1

Take care!!! This will delete all ids and reassing it, if you have external relations, this will break it!

Hope it helps!

codeigniter - How to reset auto increment on filled table?

1 Answers1