1

I can get all products that doesn't have category with this query, how can I update them with an category id considering this query to select:

   SELECT e.entity_id,e.sku
FROM catalog_product_entity AS e
LEFT JOIN catalog_category_product AS l ON l.product_id = e.entity_id
WHERE l.category_id IS NULL

I need to set

UPDATE `catalog_category_product` SET l.category_id = '132' WHERE l.category_id IS NULL

SQLFIDDLE:http://sqlfiddle.com/#!2/59b01/2

1

2 Answers 2

2

Your query seem correct, but since you update query seem to not be using an primary key you need to using SQL_SAFE_UPDATES

SET SQL_SAFE_UPDATES = 0;

update catalog_category_product AS l LEFT JOIN 
  catalog_product_entity AS e ON l.product_id = e.entity_id 
    SET l.category_id = '132
WHERE l.category_id IS NULL; 

SET SQL_SAFE_UPDATES = 1;

or

SET SQL_SAFE_UPDATES = 0;

update catalog_category_product AS l  
  SET l.category_id = '132
WHERE l.category_id IS NULL;

SET SQL_SAFE_UPDATES = 1;   
10
  • Hmm I got SET SQL_SAFE_UPDATES = 0;# MySQL returned an empty result set (i.e. zero rows). update catalog_category_product AS l LEFT JOIN catalog_product_entity AS e ON l.product_id = e.entity_id SET l.category_id = '132' WHERE l.category_id IS NULL;# MySQL returned an empty result set (i.e. zero rows). SET SQL_SAFE_UPDATES = 1;# MySQL returned an empty result set (i.e. zero rows).
    – Flak
    Oct 25, 2014 at 13:13
  • here is them fiddle: sqlfiddle.com/#!2/b5fd3/1
    – Flak
    Oct 25, 2014 at 13:16
  • Also sql_safe_updates is OFF
    – Flak
    Oct 25, 2014 at 13:18
  • Your orginal select query return zero result, so nothing to update Oct 25, 2014 at 13:20
  • Also you have create statement (if not exist) for catalog_category_product twice. Which table structure do you want to use Oct 25, 2014 at 13:28
1

I ended up using LOST PRODUCT Extension instead.

http://www.mgt-commerce.com/magento-lost-products.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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