How to modify a column to insert consequtive numbers?-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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