Trying to move all products from one category to another in PrestaShop?
Play with this sql: https://paiza.io/projects/ujEXdIzx008UEPYKziQWkQ?language=mysql. (Note to remove comments as they may trigger SQL errors in this online application)
/*
author: Wiktor Liszkiewicz
w.liszkiewicz@gmail.com
test at: https://paiza.io/projects/Rn1YBOVx4JkDo7YHu2xVtg?language=mysql
This scripts moves products from one category to another and also clenup the source category-product entry if the data is already asigned to destination category..
*/
-- create table as in PrestaShop (clone) from https://github.com/PrestaShop/PrestaShop/blob/1.7.7.x/install-dev/data/db_structure.sql
CREATE TABLE PREFIX_category_product (
id_category int(10) unsigned NOT NULL,
id_product int(10) unsigned NOT NULL,
position int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (id_category, id_product),
INDEX (id_product),
INDEX (id_category, position)
);
-- insert demo data set I
INSERT INTO PREFIX_category_product (id_category, id_product, position)
VALUES
(0, 5416, 1),
(1, 5416, 1),
(2, 5416, 1),
(3, 5416, 1),
(0, 5418, 2),
(1, 5418, 2),
(2, 5418, 2),
(3, 5418, 2);
-- insert demo data set II
INSERT INTO PREFIX_category_product (id_category, id_product, position)
VALUES
(4, 2000, 1),
(5, 2000, 1),
(4, 5000, 2),
(5, 5000, 2);
-- check table data
SELECT * FROM PREFIX_category_product;
-- enter products id manualy or fint them wi query below
-- define category
SET @OLDCAT := 4;
SET @NEWCAT := 0;
-- set @STARTINGPOS position starting number will be 0 + 1
SET @STARTINGPOS := 0;
-- or set @STARTINGPOS to the highest position value + 1 if category already have products
select @STARTINGPOS := MAX(position) from PREFIX_category_product where id_category = @NEWCAT;
-- select @STARTINGPOS; -- debug
START TRANSACTION; -- start transation
-- update category where id_product is in old category and is not in new category (cleen up later with DELETE query)
UPDATE PREFIX_category_product
SET id_category = @NEWCAT,
position = (select @STARTINGPOS := @STARTINGPOS + 1)
WHERE id_category = @OLDCAT
AND id_product IN
(SELECT * FROM (SELECT DISTINCT id_product FROM PREFIX_category_product WHERE id_category = @OLDCAT) AS t1)
and id_product NOT IN
(SELECT * FROM (SELECT DISTINCT id_product FROM PREFIX_category_product WHERE id_category = @NEWCAT) AS t2);
-- remove products and category set if not moved because they are already assigned to new category destination
DELETE FROM PREFIX_category_product WHERE id_product IN
(SELECT * FROM (SELECT DISTINCT id_product FROM PREFIX_category_product WHERE id_category = @OLDCAT) AS t3)
AND id_category = @OLDCAT;
COMMIT; -- end transation
SELECT * FROM PREFIX_category_product;Will output:
