-1

I have a table with 10 rows. Each row has an id, name and a year. Each id has a value that is incremented by 3 from the previous value. For example, the first row has an id of 1, then the next has an id of 4 etc.

After deleting the first 9 rows, the next row I added instead of having an id of 4, it continued from 31.

How can I reset the value of the next added row to start from 4 (or the last value incremented by 3)?

  • How are you incrementing the value by 3 in the first place? Is the id column in your database set to auto_increment? Need some code here, we are not going to guess and read your mind how you have this setup ;) – IncredibleHat Dec 27 '17 at 20:46
  • Possible duplicate of [Decrementing foreign key after primary key deletion SQLite](https://stackoverflow.com/questions/47838907/decrementing-foreign-key-after-primary-key-deletion-sqlite). In particular, my last comment on the question applies. – Uueerdo Dec 27 '17 at 20:47
  • 2
    Decrementing and/or reshuffling auto id's is dangerous (and generally pointless/unnecessary); especially when the increment amount is not 1, as that often is used to prevent overlap when integrating data from multiple databases. – Uueerdo Dec 27 '17 at 20:51
  • This is a really bad idea. – Strawberry Dec 27 '17 at 20:58
  • You can't set `AUTO_INCREMENT` lower than the current maximum. See https://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql – Barmar Dec 27 '17 at 21:20

1 Answers1

0

Example of a FUNCTION that calculates the new AUTO_INCREMENT value for id field of a test table by a user defined step. 

CREATE FUNCTION `test`(`step` INT) RETURNS int(11)
    NO SQL
BEGIN
IF `step`<1 THEN
SET `step` = 1;
END IF;
RETURN `step`+(SELECT MAX(`id`) FROM `test`);
END

You can use a function like that to calculate the new value for the id (AI) field when inserting new rows to the table. For example.

INSERT INTO TABLE `test` (`id`,`value`) VALUES ((SELECT `test`(4)),'yourvalue');