INSERT INTO with Increment variable

Question

I would like to have an INSERT INTO query which one of the fields I update is a calculated var 'mycount' - which will be the number of the inserted row in the query.

For example: If I insert 3 rows, I'd like this var to be '1' for the first row inserted, '2' for the second, and so forth.

SET @mycount=0;
INSERT INTO my_table
(@mycount,field1,field2,field3...)
SET @mycount=@mycount+1;
SELECT @mycount,field1,field2,field3..
FROM my_table
WHERE id IN (id1,id2,id3..);

This code returns an error. How can I declare a variable inside an INSERT INTO query and have it incremented with every row inserted ?

IMPORTANT - I do not need an AUTO-INCREMENT column - this is a part of a calculation that needs to be performed only in this specific INSERT INTO query, and it is only part of the calculation. What I need is really a calculation of (number_of_inserted_row+some_other_calculation) but I just simplified it for the sake of the question.

you can do this using add new filed which you want to insert as like count_number and define auto increment this filed and not need to insert in this query — Shafiqul Islam
– Shafiqul Islam, Commented Feb 24, 2017 at 12:20

fancyPants · Accepted Answer · 2017-02-24 14:44:26Z

4

Well, usually an auto_increment column is used for this. If you don't want to for whatever reason, you can do it like this:

INSERT INTO my_table
(your_quasi_auto_increment_column, field1, field2, field3...)
SELECT (@mycount := @mycount + 1) + <other_calculation>, field1, field2, field3..
FROM my_table
, (SELECT @mycount := 0) var_init_query_alias
WHERE id IN (id1,id2,id3..);

edited Feb 24, 2017 at 14:44

answered Feb 24, 2017 at 12:23

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Yaron Over a year ago

I cannot use auto-increment as a column cause it is a specific case only that I need it for. And the full expression uses auto-increment only as part of it. I need the field to be (calculation+number_of_inserted_row_in_current_query). so if I ran 5 inserts of 3 rows each time - it will still give me (1,2,3,4,5) instead of a constantly incrementing primary number. I tried running your query but it gives an error..

Yaron Over a year ago

I managed to run your query.. (@mycount in the first INSERT INTO statement should be a fieldname). BUT - if I want my calculation to be (@mycount+other calculations) how do I do it ?

fancyPants Over a year ago

Yes, you're right, it should be a field name of course. Copy pasted it from you and didn't notice. If your "other calculation" is resulting in a number you can calculate with simple arithmetic operators. If it's text of some sort, use CONCAT() to put the text and the number together. Do you have a specific error message? What does not work in your calculation?

Yaron Over a year ago

I want to calculate : (mycount+another_number). If I use mycount:=mycount+1+another_number it won't do cause mycount is not going to be incremented by 1 with each row added, but by the other number as well.

fancyPants Over a year ago

You have to use parantheses (see my edited answer) so that your other calculation doesn't affect the @mycount variable.

|

Shafiqul Islam · Accepted Answer · 2017-02-24 14:47:20Z

0

you can do this using add new filed which you want to insert as like count_number and define auto increment this filed and not need to insert in this query

 SET @test=1;
 INSERT INTO test (`test`,`test2`,`test3`)
 SELECT (@test := @test +1) AS `test`,`test2`,`test3`
 FROM test

if you want to add new file then then check this code

SET @count_value=0;
 INSERT INTO test (`count_value`,`test`,`test2`,`test3`)
 SELECT (@count_value := @count_value +1) AS `count_value`,`test`,`test2`,`test3`
 FROM test

in my_table1 add field count_number and type int and add auto increment then it will work

edited Feb 24, 2017 at 14:47

answered Feb 24, 2017 at 12:24

Shafiqul Islam

5,6852 gold badges36 silver badges45 bronze badges

1 Comment

Yaron Over a year ago

But I don't need a primary key.. I already I have one. It's only in the INSERT INTO query I am running that I want a certain field to receive the value of (number_of_inserted_row+another_number). When I run the query once and insert 2 rows it should be (1+another_number),(2+another_number). When I ran it again with 3 rows it should be (1+another_number),(2+another_number),(3+another_number).

Collectives™ on Stack Overflow

INSERT INTO with Increment variable

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related