What's wrong with mysql reset auto increment in codeigniter?

Hi, I'm a newbie in codeigniter. So far, I've tried to reset auto increment field in mysql using codeigniter. 

Code:

// Location: rb_model.php
function reset_ai() {
   $sql = "SET  @num := 0; UPDATE rancangan_bulanan SET id = @num := (@num+1);";
   $this->db->query($sql);
}

Unfortunately, it give me an error:

Code:

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

It's really strange, because the code was working in sql command line.

Any suggestion?

ALTER TABLE rancangan_bulanan AUTO_INCREMENT = 1

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

ALTER TABLE rancangan_bulanan AUTO_INCREMENT = 1

Thanks for your effort, but I also want the filled record's id are rearranged too. Can you help me?

Why an reset? Normaly you do a "truncate table" for an reset. but you have to think about dependences.

ALTER TABLE tablename AUTO_INCREMENT = 1

There is a very easy way with phpmyadmin under the ( operations tab ),
you can set, in the table options, autoincrement to the number you want.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

ALTER TABLE tablename AUTO_INCREMENT = 1

There is a very easy way with phpmyadmin under the ( operations tab ),
you can set, in the table options, autoincrement to the number you want.

I think OP does want to do this, but also wants to renumber all of the old records, probably to remove missing ids that were deleted? If it were me, I'd probably just rename the table, recreate the original table, and then batch insert everything. Maybe there is an easier way.

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

1. Select all records and store in array or object. Maybe also save them to a JSON file
2. Truncate the table -> This will reset auto increment
3. (Re)Insert the records from step 1 -> remove existing ID column
4. Your ID's have been rearranged