0

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.

2 Answers 2

0

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
1
  • I'm sorry but this is not right syntax, you'll need position as a primary key and I'll edit my question in order to clarify that position would have different sequences Nov 26, 2014 at 4:12
0

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]);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.