How to delete WooCommerce Products than have a specific custom field?

I'm using a plugin that adds a wholesale price to WooCommerce products.

Now I need to delete all the products that have an empty wholesale price to only keep the products I want to keep on the wholesale part of my store.

Unfortunately, WP doesn't have the possibility to filter like I want to do to display only those kind of wholesale products and then bulk delete them.

The name of the field is wholesale_customer_wholesale_prices.

How can I achieve that easily?

Thanks a lot

I'm using a plugin that adds a wholesale price to WooCommerce products.

Now I need to delete all the products that have an empty wholesale price to only keep the products I want to keep on the wholesale part of my store.

Unfortunately, WP doesn't have the possibility to filter like I want to do to display only those kind of wholesale products and then bulk delete them.

The name of the field is wholesale_customer_wholesale_prices.

How can I achieve that easily?

Thanks a lot

• custom-field
• woocommerce-offtopic

Share Improve this question edited May 15, 2018 at 18:33 Mostafa Soufi 80577 silver badges1919 bronze badges asked May 15, 2018 at 15:44 SeFiSeFi 122 bronze badges 2

• By "field", do you mean that wholesale_customer_wholesale_prices is the name of the key of the postmeta entry? – kero Commented May 15, 2018 at 16:35
• @kero yes that's it. – SeFi Commented May 16, 2018 at 9:11

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

WooCommerce products is a custom post type called product. We can use that to start for querying posts. Now you say you want to find those posts, that either don't have a postmeta entry with the key wholesale_customer_wholesale_prices or ones that have an entry that has an empty value.

DELETE p
FROM wp_posts p
INNER JOIN wp_postmeta pm
ON
    (p.ID = pm.post_id
    AND
    pm.meta_key LIKE 'wholesale_customer_wholesale_prices')
WHERE
    p.post_type LIKE 'product'
    AND
        (
            pm.meta_value LIKE ''
            OR
            pm.meta_value IS NULL
        );

This query will delete all products that have the an '' (empty string) or NULL as value for the relationship.

Now you're left with products that never had the relationship.

DELETE p
FROM wp_posts p
LEFT OUTER JOIN wp_postmeta pm
ON
    (p.ID = pm.post_id
    AND
    pm.meta_key LIKE 'wholesale_customer_wholesale_prices')
WHERE
    p.post_type LIKE 'product'
    AND
    pm.meta_key IS NULL;

---

Of course, with direct db manipulation like this, you should first test this in a test environment and not directly on the live site. This will only delete the entries in wp_posts table, there are many plugins/queries available for cleaning up the db after that (e.g. removing orphaned postmeta entries)