Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Select Duplicate SKUs from WooCommerce Database #woocommerce #mysql
SELECT meta_value
FROM wp_fanatic_shop_postmeta
WHERE meta_key = '_sku'
AND meta_value != ''
GROUP BY meta_value HAVING COUNT(meta_value) > 1
@Miragetek
Copy link

Miragetek commented Feb 24, 2022

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

@yanknudtskov
Copy link
Author

yanknudtskov commented Feb 25, 2022

Thank you 😊

@seldimi
Copy link

seldimi commented Jul 29, 2022

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

@Miragetek
Copy link

Miragetek commented Jul 29, 2022

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.

@seldimi
Copy link

seldimi commented Jul 29, 2022

wp_postmeta might contain orphan postmeta values from deleted records in wp_posts

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment