Multiple Product categorie move/update using MySQL

[DejaVu]

I currently have 10,000 products in my Home Category and need to move them into their correct categories.
I now about

UPDATE `ps_category_product` SET `id_category` = 3 WHERE `id_category` = 2

But I dont want to move them all at once to 1 Category, I would like to specify the product id's to move.

Lets say products ID's 1, 2, 4, 7, 8, 12, 16, 19, 33 and 42 are in the Home category and I want to move them into Category ID 4. How can I do that with an 1 line of SQL?

And, is it possible to make another line of SQL for different products to Copy to another category?
IE -

Lets say products ID's 3, 5, 6, 9, 10 and 11 - I wanted to stay in the Home category but also wanted them in the Category ID 3? Is there a SQL Query for that?

I imagine there is as MySQL is very versatile or so I've read and is relatively easy to understand once you get the basics. I'm just a little new to it and need a helping hand - cos I'm a bit thick!

Thanks guys!

[rocky]

The following query should move products 1, 2, 4, 7, 8, 12, 16, 19, 33 and 42 from category 1 to category 4:

UPDATE `ps_category_product` SET `id_category` = 4 WHERE `id_category` = 1 AND `id_product` = 1 OR `id_product` = 2 OR `id_product` = 4 OR `id_product` = 7 OR `id_product` = 8 OR `id_product` = 12 OR `id_product` = 16 OR `id_product` = 19 OR `id_product` = 33 OR `id_product` = 42

The following query should copy products 3, 5, 6, 9, 10 and 11 from category 1 to category 3 and give them the appropriate sequential position 1, 2, 3, etc:

SET @position = 0;
INSERT INTO `ps_category_product` SELECT 3, `id_product`, @position:=@position+1 as `position` FROM `ps_category_product` WHERE `id_category` = 1 AND `id_product` = 3 OR `id_product` = 5 OR `id_product` = 6 OR `id_product` = 9 OR `id_product` = 10 OR `id_product` = 11

Change ps_ to your database prefix. Note that you must copy all the products in the one query for the position variable to work. Make sure you back up your tables before running any queries in case anything goes wrong. I'm known to make mistakes sometimes in complicated queries like these. ;-P

[Geunix]

Thanks rocky! this is really useful, especially when 13000 products are being imported from CSV directly into Home category and they are not edited!
It would help a lot if I could use a formula for products with ID's between 1886 and 15178. Can you help me do such a query?

[kalakrima]

The following query should move products 1, 2, 4, 7, 8, 12, 16, 19, 33 and 42 from category 1 to category 4:

UPDATE `ps_category_product` SET `id_category` = 4 WHERE `id_category` = 1 AND `id_product` = 1 OR `id_product` = 2 OR `id_product` = 4 OR `id_product` = 7 OR `id_product` = 8 OR `id_product` = 12 OR `id_product` = 16 OR `id_product` = 19 OR `id_product` = 33 OR `id_product` = 42

The following query should copy products 3, 5, 6, 9, 10 and 11 from category 1 to category 3 and give them the appropriate sequential position 1, 2, 3, etc:

SET @position = 0;
INSERT INTO `ps_category_product` SELECT 3, `id_product`, @position:=@position+1 as `position` FROM `ps_category_product` WHERE `id_category` = 1 AND `id_product` = 3 OR `id_product` = 5 OR `id_product` = 6 OR `id_product` = 9 OR `id_product` = 10 OR `id_product` = 11

Change ps_ to your database prefix. Note that you must copy all the products in the one query for the position variable to work. Make sure you back up your tables before running any queries in case anything goes wrong. I'm known to make mistakes sometimes in complicated queries like these. ;-P

 

 

Hello,

 

thank you for sharing this with us. I would like to know if it's possible to do this :

 

I have 2 categories of products : A & B, they have the same products (references and images), but have different prices.

So I tried to use your code, but when the products are duplicated they keep the same id_product and they are assign to category A & B. SO if I change the price of one product in category B, the price change in category A too.

 

I would like to duplicate the products from category A to category B, but with an different ID and the duplicated products are assigned ONLY to the category B... I have 1000 products and do this manually is difficult. I would like to do this with SQL.

 

I'm not sure if i express my needs well, sorry for my bad english, I hope some one could help me.

[northriver]

I'm getting the following SQL return statement when running you're copy command with the updated products and categories:

 

#1062 - Duplicate entry '27-23' for key 'PRIMARY'

 

Any suggestions?

 

-Jesse

 

SQL:

 

SET @position = 0;INSERT INTO `ps_category_product` SELECT 27, `id_product`, @position:=@position+1 as `position` FROM `ps_category_product` WHERE `id_category` = 68 AND `id_product` = 306 OR `id_product` = 305 OR `id_product` = 303 OR `id_product` = 304 OR `id_product` = 375 OR `id_product` = 301 OR `id_product` = 483 OR `id_product` = 23

 

 

 

The following query should move products 1, 2, 4, 7, 8, 12, 16, 19, 33 and 42 from category 1 to category 4:
 

UPDATE `ps_category_product` SET `id_category` = 4 WHERE `id_category` = 1 AND `id_product` = 1 OR `id_product` = 2 OR `id_product` = 4 OR `id_product` = 7 OR `id_product` = 8 OR `id_product` = 12 OR `id_product` = 16 OR `id_product` = 19 OR `id_product` = 33 OR `id_product` = 42

The following query should copy products 3, 5, 6, 9, 10 and 11 from category 1 to category 3 and give them the appropriate sequential position 1, 2, 3, etc:

SET @position = 0;INSERT INTO `ps_category_product` SELECT 3, `id_product`, @position:=@position+1 as `position` FROM `ps_category_product` WHERE `id_category` = 1 AND `id_product` = 3 OR `id_product` = 5 OR `id_product` = 6 OR `id_product` = 9 OR `id_product` = 10 OR `id_product` = 11

Change ps_ to your database prefix. Note that you must copy all the products in the one query for the position variable to work. Make sure you back up your tables before running any queries in case anything goes wrong. I'm known to make mistakes sometimes in complicated queries like these. ;-P

 

[saintofinternet]

i would like to add 2 categories to all product id's

 

how can i achieve that with SQL query?

 

i need this so i can keep the products in home category for being displayed on website homepage.

Edited January 11, 2016 by saintofinternet (see edit history)