2

I have a table named users_items. In this table there are 3 columns. 1 of them is called id. There are like 100.000 - 150.000 data in this table. id is set to AUTO_INCREMENT.

I want to reset all id's to 0 and than replace with numbers 1,2,3,4,5,6 continue like that.

kRiZ
  • 2,320
  • 4
  • 28
  • 39
Ali Güdüz
  • 33
  • 3
  • you want to insert your own id for each row? – Harry Dec 14 '14 at 22:10
  • And you want to keep the data doing this? – kRiZ Dec 14 '14 at 22:14
  • I want to keep data but only change id's for example 108843 make it 1 and make next one 2 like that – Ali Güdüz Dec 14 '14 at 22:16
  • [Try this](http://www.mysqltutorial.org/mysql-reset-auto-increment) you may find something – Fas M Dec 14 '14 at 22:33
  • While it is clear what you want to do, try to put questions in the form of a question. Just a heads up. In specific with your question, there are a lot of factors to consider in doing that. I would back up the table before you do anything like this to it. – peege Dec 14 '14 at 22:34

2 Answers2

0

To start from one simply do the following: 

  ALTER TABLE tablename AUTO_INCREMENT = 1

IF need reference use following links:

altering table: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Auto increament/Reset primary key: Reorder / reset auto increment primary key

Mysql reset autoincreament: http://www.mysqltutorial.org/mysql-reset-auto-increment

Community
  • 1
  • 1
TMNT
  • 156
  • 1
  • 10
  • Thanks for repyling but I want to reset auto_increment its correct but I want to apply auto_increment to all rows. – Ali Güdüz Dec 14 '14 at 22:23
0

You won't be able to make all of them 0 at the same time, as you can't have duplicates for the PK.

Create a copy of the table using phpMyAdmin or any tool you want or using SQL queries.

Then delete all data from the original table using: 

DELETE users_items

Or:

TRUNCATE users_items

Then reset auto increment using:

ALTER TABLE users_items AUTO_INCREMENT = 1

If you used TRUNCATE then you won't have to reset the auto increment counter.

After this you can use SELECT and INSERT to get the data from the copied table back to this one:

INSERT INTO users_items (col2, col3...) SELECT col2, col3,... FROM users_items_copy

(Note: the id column was not touched while selecting and inserting rows.)

kRiZ
  • 2,320
  • 4
  • 28
  • 39