CODEWITHSUNDEEP
phpmysqlopencartsql-deletesql-in
John Dow
John Dow

Reputation: 19

How to remove categories with no products?

I am a bit stuck on the following SQL:

delete  from oc_category_description where 'category_id' NOT in (SELECT category_id FROM oc_product_to_category);
delete from oc_category_path where 'category_id' NOT in(SELECT category_id from oc_product_to_category);
delete from oc_category_to_store where 'category_id' NOT in(SELECT category_id from oc_product_to_category);
delete from oc_category_to_layout where 'category_id' NOT in(SELECT category_id from oc_product_to_category);
delete from oc_category_filter where 'category_id' NOT in(SELECT category_id from oc_product_to_category);
delete from oc_coupon_category where 'category_id' NOT in(SELECT category_id from oc_product_to_category);
delete from oc_category where 'category_id' NOT in(SELECT category_id from oc_product_to_category);

It removes all data ignoring the where part. What am I doing wrong?

Upvotes: 1

Views: 235

Answers (1)

GMB
GMB

Reputation: 222702

You need to remove the single quotes around the column name, otherwise it becomes a literal string, and the expression does not do what you want. It actually checks if literal string 'category_id' belongs to the resulset of the subquery: since it probably does not, all rows get deleted.

Also, I would recommend using exists, which is null-safe, while not in is not (that is, if any value in the subquery is null, all rows will be deleted).

delete from oc_category 
where not exists (
    select 1 
    from oc_product_to_category pc 
    where pc.category_id = oc_category.category_id
);

For performance, consider an index on oc_product_to_category(category_id) (or at least a compound index where this column appears first).

Upvotes: 3

Related Questions