Menu
In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.
You use TRANCATE table to empty the table. TRUNCATE not only deletes the rows but resets the auto increment value by design. Use DELETE FROM table instead.
The TRUNCATE TABLE statement removes all the data from a table and resets the auto-increment value to zero.
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
Issue the following command to reseed mytable to start at 1:
DBCC CHECKIDENT (mytable, RESEED, 0)
Read about it in the Books on Line (BOL, SQL help). Also be careful that you don't have records higher than the seed you are setting.
DBCC CHECKIDENT('databasename.dbo.tablename', RESEED, number)
if number=0 then in the next insert the auto increment field will contain value 1
if number=101 then in the next insert the auto increment field will contain value 102
Some additional info... May be useful to you
Before giving auto increment number in above query, you have to make sure your existing table's auto increment column contain values less that number.
To get the maximum value of a column(column_name) from a table(table1), you can use following query
SELECT MAX(column_name) FROM table1
semi idiot-proof:
declare @max int;
select @max = max(key) from table;
dbcc checkident(table,reseed,@max)
http://sqlserverplanet.com/tsql/using-dbcc-checkident-to-reseed-a-table-after-delete
If you're using MySQL, try this:
ALTER TABLE tablename AUTO_INCREMENT = 1
I figured it out. It's:
DBCC CHECKIDENT ('tablename', RESEED, newseed)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With