How to increment a field in mysqli

Question

I want to insert many lines, but in one column I would like it to increment. In my example I am setting a variable to 5000 and I would like it to increment by one, every time a line is added to the database.

I will say now this has nothing to do with auto increment. There is a reason I need an incrementing field.

Here is my insert query

$incrementField=5000;
$query="INSERT INTO `table` (`incrementField`,`this`,`morestuff`)
    SELECT ?,`this`,`morestuff` FROM `someTable`";
$stmt = $db->prepare($query);
$stmt->bind_param('i',$incrementField);
$stmt->execute();
$stmt->close();

Where the ? is I would like it to increment. I have tried ?+1 ?.+1 and all kinds of permutations, but nothing seems to work.

And where is the loop that will make this run more than once anyway — RiggsFolly, Jul 13 '17 at 15:54
I already have an auto incrementing id. This is nothing to do with the id. It is just a field that I wish to increment starting at 5000, and then 5001, and 5002 on each entry. The table it is getting the select from has multiple lines and I want this field to increment on every line inserted — Thomas Williams, Jul 13 '17 at 15:56
Are you saying the actual column names are 5000, 5001, and 5002? — Don't Panic, Jul 13 '17 at 16:12
No the column name is `incrementField`. For every line inserted I wish to increment a field by 1 starting at 5000. This query is run once and can insert something like 200,000 files. Basically I am taking data from a tempory table and inserting it into another table. For every line inserted I want to increment this field. I don't want to resort to a php loop as this would slow it down tremendously. — Thomas Williams, Jul 13 '17 at 16:55
See this to start auto increment from `5000`. https://stackoverflow.com/questions/1485668/how-to-set-initial-value-and-auto-increment-in-mysql — pro_cheats, Jul 13 '17 at 17:18
I already have one auto increment field for my id, so I didn't think you could have 2. Also I can't have this field as an auto increment field. As this is a number that could have repeated numbers you see, and with auto increment you have to have unique numbers. You have given me an idea though. I can set an auto increment level on my temporary table that I am reading from. Then use that id to insert into this table. However i would like to know if it is possible to do what I have asked — Thomas Williams, Jul 13 '17 at 17:30
This looks kind of similar to what you're asking. Not sure if it's exactly the same thing, but might be helpful: https://stackoverflow.com/questions/19655860/how-to-use-insert-select-with-a-particular-column-auto-incrementing-startin — Don't Panic, Jul 13 '17 at 17:35

score 0 · Answer 1 · answered Jul 13 '17 at 18:13

0

You definitely should use SQL AUTO_INCREMENT logic. I guess that columns supposed to be a unique identifier which is also known as PRIMARY KEY.

answered Jul 13 '17 at 18:13

boesing

345
3
10

No it isn't a unique identifier. In this column numbers can be repeated. I already have a unique identifier called id which is already set to auto complete, and you can't have two autocomplete fields in a table because an auto complete is the primary key – Thomas Williams Jul 13 '17 at 18:46

Thomas Williams · Accepted Answer · 2017-08-07T13:56:47.963

I now have an answer to this that works properly

$incrementField=5000;
$query="INSERT INTO `table` (`incrementField`,`this`,`morestuff`)
    SELECT @position := ifnull(@position, ?) + 1),`this`,`morestuff` FROM `someTable`";
$stmt = $db->prepare($query);
$stmt->bind_param('i',$incrementField);
$stmt->execute();
$stmt->close();

So it can be done after all. I think the more people that tell me it can't be done only encourages me to find a solution even more. So to all those who said it can't be done, it can.

How to increment a field in mysqli

2 Answers2