How to modify a column to insert consequtive numbers?

Question

So I have a table where a column that was given an auto_increment value accidentally got started form 300 instead of 1,2,3,4......i'm a beginner and i do not know how to change it back to 1,2,3,4......screenshot of table

how to change the 307, 308 to 1,2,3,4...?

I tried to update the table but that did not work.

It's an arbitrary unique identifier so why are you concerned about what it starts from? — user1191247, Jan 29 '23 at 12:20
This really does not matter in the slightest, I doubt you're going to run out of numbers. You could update the column but this is probably your PK and used for other foreign keys, so I would suggest not. — Stu, Jan 29 '23 at 12:34
@Stu, it can matter, depending on what the table is and what country we are talking about. For instance in Singapore, invoice numbers must be contiguous, otherwise the tax auditor gets very annoyed. We struck this when we sold our software in Singapore and the client got audited. 1) we were keeping some special invoices in the low numbers as templates, so there was a gap of 90. 2) Rollbacks make a hole in the numbering. — Rohit Gupta, Jan 29 '23 at 13:42
@RohitGupta It's a pretty much "it depends" situation but for the purpose of a unique primary key it should not matter - if an application requires a sequential id value this can be generated by other means and can't rely on a primary key where a row may be deleted or rolled back due to error etc. — Stu, Jan 29 '23 at 13:48

score 1 · Answer 1 · answered Jan 29 '23 at 12:34

1

Step-1) First take backup of your table data.

Step-2) Truncate the table by using the below SQL query.

TRUNCATE TABLE [Your_Table_Name];

Step-3) then again insert the into your table using backup data.

answered Jan 29 '23 at 12:34

Deepak Tewatia

34
2

score 0 · Answer 2 · answered Jan 29 '23 at 12:31

0

Alter table to drop the auto_increment, update, alter table to add the auto_increment

drop table if exists t;
create table t
( id int auto_increment primary key, val int);

insert into t values
(307,1),(308,1),(309,1),(310,1),(311,1);

alter table t
    modify column id int;
    #drop primary key;
show create table t;
update t 
    set id = id - 306;

alter table t
    modify column id int auto_increment;    

show create table t;

https://dbfiddle.uk/eBQh6cj8

answered Jan 29 '23 at 12:31

P.Salmon

17,104
2
12
19

Plain update is ok https://dbfiddle.uk/IbPUUiab – Serg Jan 29 '23 at 12:36
@serg auto_increment value does not change in your fiddle and I suspect resetting that is the point of the question.... – P.Salmon Jan 29 '23 at 12:38
Yes, plain update will not reset autoincrement settings (current). Not sure it was required. – Serg Jan 29 '23 at 12:50

score 0 · Answer 3 · answered Jan 29 '23 at 16:46

With MySQL 8.0 you can use a window function to calculate the row numbers and then update the table:

mysql> select * from t;
+-----+------+
| id  | val  |
+-----+------+
| 307 |    1 |
| 308 |    1 |
| 309 |    1 |
| 310 |    1 |
| 311 |    1 |
+-----+------+

mysql> with cte as ( select id, row_number() over () as rownum from t )
    -> update t join cte using (id) set id = rownum;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from t;
+----+------+
| id | val  |
+----+------+
|  1 |    1 |
|  2 |    1 |
|  3 |    1 |
|  4 |    1 |
|  5 |    1 |
+----+------+

Then make sure the next id won't be a high value:

mysql> alter table t auto_increment=1;

You can try to set the auto_increment to 1, MySQL will automatically advances that to the highest id value in the table, plus 1.

Be aware that this doesn't guarantee subsequent rows will use consecutive values. You can get non-consecutive values if:

You insert greater values explicitly, overriding the auto-increment.
You roll back transactions. Id values generated by auto-increment are not recycled if you roll back.
You delete rows.
Occasionally InnoDB will skip a number anyway. It does not guarantee consecutive values — it only guarantees unique values. You should not rely on the auto-increment to be the same as a row number.

score 0 · Answer 4 · answered Jan 30 '23 at 10:50

Here is a one approach to your problem.

Please take note of the following points before proceeding:

Take backup of your table in-case things do not go as expected.
Below test case has been performed on MySQL 5.7 and MyISAM Engine.

Step1: Generating dummy test table as per your test case.

   
    mysql> CREATE TABLE t (  
        ->   `Id` int(11) NOT NULL AUTO_INCREMENT,  
        ->   `product_id` int(11) DEFAULT 0,  
        ->   PRIMARY KEY (`Id`)  
        -> ) ENGINE=MyISAM;  
    Query OK, 0 rows affected (0.03 sec)  
      
    -- Inserting dummy data  
      
    mysql> INSERT INTO t VALUES (300,1);  
    Query OK, 1 row affected (0.00 sec)  
      
    mysql> INSERT INTO t VALUES (302,1);  
    Query OK, 1 row affected (0.00 sec)  
      
    mysql> INSERT INTO t VALUES (305,1);  
    Query OK, 1 row affected (0.00 sec)  
      
    -- Checking auto_increment value  
      
    mysql> show create table t;  
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
    | Table | Create Table                                                                                                                                                                  |  
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
    | t     | CREATE TABLE `t` (  
      `Id` int(11) NOT NULL AUTO_INCREMENT,  
      `product_id` int(11) DEFAULT '0',  
      PRIMARY KEY (`Id`)  
    ) ENGINE=MyISAM AUTO_INCREMENT=306 DEFAULT CHARSET=latin1 |  
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
    1 row in set (0.00 sec)  
      
    mysql> INSERT INTO t (product_id) VALUES (2);  
    Query OK, 1 row affected (0.01 sec)  
      
    -- Below is the resultant table for which we need Id starting from 1,2,3 and so on...  
    mysql> SELECT * FROM t;  
    +-----+------------+  
    | Id  | product_id |  
    +-----+------------+  
    | 300 |          1 |  
    | 302 |          1 |  
    | 305 |          1 |  
    | 306 |          2 |  
    +-----+------------+  
    4 rows in set (0.00 sec)

Step2: Remove AUTO_INCREMENT for the column and set the Ids manually.

    -- Remove AUTO_INCREMENT  
    mysql> ALTER TABLE t MODIFY COLUMN Id int(11) NOT NULL;  
    Query OK, 4 rows affected (0.00 sec)  
    Records: 4  Duplicates: 0  Warnings: 0  
      
    -- Set the Id manually starting from 1  
    mysql> SET @i = 0;UPDATE t SET id = @i :=@i +1;  
    Query OK, 0 rows affected (0.00 sec)  
      
    Query OK, 5 rows affected (0.00 sec)  
    Rows matched: 5  Changed: 5  Warnings: 0  
      
    -- Below is the updated table with Id starting from 1,2,3 and so on...  
    mysql> SELECT * FROM t;  
    +----+------------+  
    | Id | product_id |  
    +----+------------+  
    |  1 |          1 |  
    |  2 |          1 |  
    |  3 |          1 |  
    |  4 |          2 |  
    |  5 |          2 |  
    +----+------------+  
    5 rows in set (0.00 sec)

Step3: Enable AUTO_INCREMENT again for future record insertions.

    -- Enable AUTO_INCREMENT again for future record insertions.  
    mysql> ALTER TABLE t MODIFY COLUMN Id int(11) NOT NULL AUTO_INCREMENT;  
    Query OK, 5 rows affected (0.01 sec)  
    Records: 5  Duplicates: 0  Warnings: 0  
      
    -- Set the AUTO_INCREMENT value to continue from highest value of id in the table.  
    mysql> SELECT MAX(id+1) FROM t;  
    +-----------+  
    | MAX(id+1) |  
    +-----------+  
    |         6 |  
    +-----------+  
    1 row in set (0.00 sec)  
      
    mysql> ALTER TABLE t AUTO_INCREMENT=6;  
    Query OK, 5 rows affected (0.00 sec)  
    Records: 5  Duplicates: 0  Warnings: 0  
      
    -- Table is successfully modified and will have future records inserted with no gaps in Id's    
    mysql> INSERT INTO t (product_id) VALUES (5);    
    Query OK, 1 row affected (0.00 sec)    
      
    mysql> SELECT * FROM t;    
    +----+------------+  
    | Id | product_id |  
    +----+------------+  
    |  1 |          1 |  
    |  2 |          1 |  
    |  3 |          1 |  
    |  4 |          2 |  
    |  5 |          2 |  
    |  6 |          5 |  
    +----+------------+  
    6 rows in set (0.00 sec)

score -1 · Answer 5 · answered Jan 29 '23 at 13:07

The DBCC CHECKIDENT management command is used to reset identity counter

 DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value]}}])
 [ WITH NO_INFOMSGS ]


EXample:
     DBCC CHECKIDENT ('TestTable', RESEED, 0)
     GO

many times we need to just reseed to next Id available

declare @max int
select @max=max([Id]) from [TestTable]
if @max IS NULL   --check when max is returned as null
SET @max = 0
DBCC CHECKIDENT ('[TestTable]', RESEED, @max)

This will check the table and reset to the next ID.

You can get help from the link below: Reset identity seed after deleting records in SQL Server

My mother says: the mountain that can be seen is not far away, don't stop trying

This answer uses Microsoft SQL Server syntax, but the question was about MySQL. — Bill Karwin, Jan 29 '23 at 16:38

How to modify a column to insert consequtive numbers?

5 Answers5