Select Duplicate SKUs from WooCommerce Database #woocommerce #mysql

woocommerce-duplicate-skus.sql

# SELECT any post_status
SELECT meta_value,COUNT(meta_value),GROUP_CONCAT(DISTINCT post_id ORDER BY post_id SEPARATOR ',') post_id
FROM wp_postmeta
WHERE meta_key = '_sku'
AND meta_value != ''
GROUP BY meta_value HAVING COUNT(meta_value) > 1

# SELECT only from products that are already published or in draft
SELECT meta_value,COUNT(meta_value),GROUP_CONCAT(DISTINCT post_id ORDER BY post_id SEPARATOR ',') post_id
FROM wp_postmeta
WHERE meta_key = '_sku'
AND meta_value != ''
AND post_id IN (SELECT ID FROM wp_posts WHERE post_status IN('draft', 'publish'))
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