CODEWITHSUNDEEP
mysqlsqldatabaseinnodb
sandboxj
sandboxj

Reputation: 1254

MySQL: How to update auto-increment for all existing entries?

I have an existing table that uses an auto-incremented id as its primary key.

There are entries in the table with the id starting at 1:

id    field1
==    ======
1     foo1
2     foo2
3     foo3

Is there a way to update the id for all existing entries so the auto_increment starts at another number?:

id    field1
====    ======
1000     foo1
1001     foo2
1002     foo3

(The order does not necessarily have to be kept if that is not possible)

Upvotes: 2

Views: 3624

Answers (2)

user9326853
user9326853

Reputation:

In Sql Server it is work, MySql I'm not sure

DECLARE @NewRowId int = 998;

DBCC CHECKIDENT('MyTableName', RESEED, @NewRowId)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can use update to change the values and alter table to update the auto increment to a new value:

alter table t set auto_increment = 1003;  -- the next value

update t
    set id = id + 999;

Upvotes: 7

Related Questions