How to massively change the default category for all products in PrestaShop

The default category to which a product is assigned can be used by external modules, search engines, filters or synchronization systems. If a product's default category is set incorrectly, unexpected problems may arise when updating or accessing the product.

The most common case is when a product is assigned a main category as the default category. If you want to mass-edit the default categories of all products in PrestaShop, run the following query on the database the store uses.

NOTE We tested the query on PrestaShop 1.6.1.6:

This query will change the default category of each product to the same category to which the product is most deeply assigned:

UPDATE `ps_product_shop` SET `id_category_default` = (

SELECT `ps_category`.`id_category`

FROM `ps_category`

JOIN `ps_category_product` ON (`ps_category_product`.`id_category` = `ps_category`.`id_category`)

WHERE `ps_category_product`.`id_product` = `ps_product_shop`.`id_product`.

ORDER BY `ps_category`.`level_depth` DESC, `ps_category`.`id_parent` DESC

LIMIT 1

);

UPDATE `ps_product` SET `id_category_default` = (

SELECT `ps_category`.`id_category`

FROM `ps_category`

JOIN `ps_category_product` ON (`ps_category_product`.`id_category` = `ps_category`.`id_category`)

WHERE `ps_category_product`.`id_product` = `ps_product`.`id_product`.

ORDER BY `ps_category`.`level_depth` DESC, `ps_category`.`id_parent` DESC

LIMIT 1

);

If you have a problem with updating the product database in bulk, you can contact us.

You can also use the importer module. With it you will integrate your store with any CSV or XML file and with any wholesaler from Poland and Europe. Preparing product information in a CSV (Excel) file, you can efficiently assign entire category paths to products and add new products.