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;
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