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

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)

Related Posts: