-
-
Save yanknudtskov/035dbb2bcfacf8afa76b63d4ca6735aa to your computer and use it in GitHub Desktop.
# 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; |
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
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