Use an image in category if no image exists from a product inside that category
Code: Select all
UPDATE oc_category U
INNER JOIN (
SELECT c.category_id, MIN(p2c.product_id) product_id, p.image FROM oc_category c
INNER JOIN oc_category_description cd ON c.category_id = cd.category_id
INNER JOIN oc_product_to_category p2c ON c.category_id = p2c.category_id
INNER JOIN oc_product p ON p2c.product_id = p.product_id
GROUP BY c.category_id
) A ON U.category_id = A.category_id
SET U.image = A.image
WHERE U.image = '' OR U.image IS NULL;
Code: Select all
SELECT cd.name, c.`status` FROM oc_category c
INNER JOIN oc_category_description cd ON c.category_id = cd.category_id
LEFT OUTER JOIN oc_product_to_category p2c ON c.category_id = p2c.category_id
WHERE p2c.product_id IS NULL and c.parent_id > 0
Code: Select all
UPDATE oc_category c
INNER JOIN oc_category_description cd ON c.category_id = cd.category_id
LEFT OUTER JOIN oc_product_to_category p2c ON c.category_id = p2c.category_id
SET c.status = 0
WHERE p2c.product_id IS NULL and c.parent_id > 0;
