Find products with duplicate SKUs in WooCommerce

WooCommerce normally prevents the same SKU from being assigned to multiple products. However, duplicates may still appear after an import, synchronization with third-party software, or a direct database update.

These duplicates can disrupt inventory management or prevent a product from being updated. To locate them quickly, you can query the wp_postmeta table, where WooCommerce stores SKUs under the _sku key.

List SKUs assigned more than once

The following query returns every non-empty SKU assigned to multiple products:

1SELECT meta_value AS sku, COUNT(DISTINCT post_id) AS products
2FROM wp_postmeta
3WHERE meta_key = '_sku'
4  AND meta_value != ''
5GROUP BY meta_value
6HAVING COUNT(DISTINCT post_id) > 1
7ORDER BY products DESC;

The products column shows how many products share each SKU. If your WordPress installation uses a table prefix other than wp_, remember to adjust the wp_postmeta table name.

Identify the affected products

Once you have found a duplicate SKU, use this second query to identify the products and variations to which it is assigned. Simply replace SKU-TO-FIND with a value returned by the previous query:

 1SELECT
 2  p.ID,
 3  p.post_title,
 4  p.post_type,
 5  p.post_status,
 6  pm.meta_value AS sku
 7FROM wp_posts AS p
 8INNER JOIN wp_postmeta AS pm ON pm.post_id = p.ID
 9WHERE pm.meta_key = '_sku'
10  AND pm.meta_value = 'SKU-TO-FIND'
11ORDER BY p.ID;

The post_type column distinguishes standard products (product) from variations (product_variation). You can then use the value in the ID column to locate each item in the WordPress administration interface.

These queries are for diagnostic purposes only and do not modify any data. To fix duplicates, I prefer updating products through WooCommerce or correcting the import source rather than deleting rows directly from the database. This allows WooCommerce to update its related data and caches properly.

As always, make a backup before working on the database.