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
  • What error msg are you getting? – Renon Stewart Oct 25 '14 at 13:06
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). – Flakerim Oct 25 '14 at 13:13
  • here is them fiddle: sqlfiddle.com/#!2/b5fd3/1 – Flakerim Oct 25 '14 at 13:16
  • Also sql_safe_updates is OFF – Flakerim Oct 25 '14 at 13:18
  • Your orginal select query return zero result, so nothing to update – Renon Stewart Oct 25 '14 at 13:20
  • Also you have create statement (if not exist) for catalog_category_product twice. Which table structure do you want to use – Renon Stewart Oct 25 '14 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, privacy policy and cookie policy

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