CODEWITHSUNDEEP
mysqlrowincrement
MFIHRI
MFIHRI

Reputation: 307

How to get mysql to auto increment starting from the previous row value

Thank you in advance for your help on this one.

Here's my issue:

Table A had spam accounts that I deleted. The rows were like: 1, 2556, 2559, 2565, 2595, etc.

Am trying to import Table A into the empty Table B in the same database. tables have different fields.

Table B declines the import because the insert is set to auto increment. Mysql table B does not seem to want to skip rows.

I went to Table A and updated the rows to:

1, 2, 3,4,5..., 18

Now when I try to create a user in Table A using PyphAdmin with user_id value of NULL, the id i get is 2596. Mysql still remembers the old incrementing sequence. How can I get Mysql table to auto-increment based on the previous row so the new id will be 19 ?

The mysql import file is encrypted in phpshield so I dont see what's going on and has no way to edit it. But I assume this is what is happening after I ruled out all other possibilities.

Upvotes: 0

Views: 1981

Answers (3)

Mohit Mehta
Mohit Mehta

Reputation: 1285

ALTER TABLE TableA AUTO_INCREMENT=19;

Upvotes: 0

ganesh
ganesh

Reputation: 1066

Try this

ALTER TABLE A AUTO_INCREMENT=19;

Upvotes: 0

Devart
Devart

Reputation: 122042

  1. The field must be primary key - PRIMARY KEY (id)
  2. The field must be AUTO_INCREMENT
  3. Reset table's autoincrement value.

For example -

CREATE TABLE table1(
  id INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
);

Set new autoincrement value -

ALTER TABLE table1 AUTO_INCREMENT = 19;

Upvotes: 3

Related Questions