MySQL insert position value

Question

I have trouble while inserting the values corresponding to a column: position. I have multiple inserts like this:

INSERT INTO category_product(id_category,id_product,position) VALUES(2,1,1);
INSERT INTO category_product(id_category,id_product,position) VALUES(2,2,2);
INSERT INTO category_product(id_category,id_product,position) VALUES(2,3,3);
INSERT INTO category_product(id_category,id_product,position) VALUES(3,1,0);
INSERT INTO category_product(id_category,id_product,position) VALUES(3,3,1);

But I would like to have something like an insert within a select, I've tried:

SET @position := 0;
INSERT INTO category_product(id_category,id_product,position) 
(SELECT 2,id_product,@position+1 FROM product);

SET @position := -1;
INSERT INTO category_product(id_category,id_product,position) 
(SELECT 3,id_product,@position+1 FROM product WHERE [a condition to get id_product 1, 3]);

My problem: it doesn't work like I want and my main reason for this is I'm using a loop to make all the inserts (using a Cursor) and they're just killing me with the time. Any suggestion would be great.

Answer 1

Create Auto Increment Position Column when you Create product TABLE

EXAMPLE

               Create Table category_product
               ( 
              id_category int,
              id_product int,
               position int Auto_Increment=1
               ) 

Then Simple Insert

    Insert into category_product
     select 2,id_product from Product

Answer 2

As some days passed, I've checked my queries and got my mistake: Wrong use of User-Defined Variables

As this: @position+1 give me always 1 because it's not an increasing value, it's just the same variable +1... in order to make it an increasing value it must be assigned the new increased value: @position := @position+1

SET @position := 0;
INSERT INTO category_product(id_category,id_product,position) 
(SELECT 2,id_product,@position := @position+1 FROM product);

SET @position := -1;
INSERT INTO category_product(id_category,id_product,position) 
(SELECT 3,id_product,@position := @position+1 FROM product 
  WHERE [a condition to get id_product 1, 3]);