Select Duplicate SKUs from WooCommerce Database #woocommerce #mysql

woocommerce-duplicate-skus.sql

SELECT meta_value
FROM wp_fanatic_shop_postmeta
WHERE meta_key = '_sku'
AND meta_value != ''
GROUP BY meta_value HAVING COUNT(meta_value) > 1

Slightly more useful version.
Change select line to the following
select meta_value,COUNT(meta_value),GROUP_CONCAT(DISTINCT post_id ORDER BY post_id SEPARATOR ',') post_id

This returns the sku, how many duplicates, csv list of duplicate id's

Thank you 😊

Making it even better for existing posts.

select meta_value,COUNT(meta_value),GROUP_CONCAT(DISTINCT post_id ORDER BY post_id SEPARATOR ',') post_id
FROM wp_postmeta
JOIN wp_posts ON wp_posts.ID=wp_postmeta.post_id
WHERE meta_key = '_sku'
AND meta_value != ''
GROUP BY meta_value HAVING COUNT(meta_value) > 1

Making it even better for existing posts.

select meta_value,COUNT(meta_value),GROUP_CONCAT(DISTINCT post_id ORDER BY post_id SEPARATOR ',') post_id FROM wp_postmeta JOIN wp_posts ON wp_posts.ID=wp_postmeta.post_id WHERE meta_key = '_sku' AND meta_value != '' GROUP BY meta_value HAVING COUNT(meta_value) > 1

Hi, why join against the posts table? postmeta contains the ID of the existing posts already. Joining to posts just increases the amount of data the query is pulling for no reason.

wp_postmeta might contain orphan postmeta values from deleted records in wp_posts