Skip to content

Finding products with duplicate variations in WooCommerce

Oh no, you’ve just re-imported a product CSV without checking the “update” flag and now some products have duplicate variations. Here’s what to do.

When populating a WooCommerce store, a CSV can be faster than using the WordPress CMS. Suppose that you’ve already loaded your product sheet when you add more products to your CSV. You can re-import the CSV and WooCommerce will ignore existing products unless the update flag is checked.

The WooCommerce CSV spec allows variations to not have their own SKU. This is fine, but if your variation lines in the CSV don’t have an ID or SKU, this will cause existing variations to not be ignored when re-imported, resulting in duplicate variations on a product.

Duplicated variations on a WooCommerce product

I did this while loading hundreds of items from various sheets and wasn’t immediately aware of this import behaviour as confirmed by plugin support. I had to find and manually correct all of the affected products.

Here’s a quick query that I came up with to help me find products that have duplicated variations.

FROM wp_postmeta vmeta
JOIN wp_posts v ON v.ID = vmeta.post_id
JOIN wp_posts p ON p.ID = v.post_parent
WHERE p.post_type = "product"
AND vmeta.meta_key LIKE "attribute_%"
GROUP BY p.ID, vmeta.meta_value
HAVING COUNT(vmeta.meta_value) > 1

This generates a list of product IDs with duplicated variations, which you can then conveniently plug into WordPress’ URL structure for quick access.