CODEWITHSUNDEEP
mysqlsql
Dylan Cross
Dylan Cross

Reputation: 5986

WordPress WooCommerce delete items with duplicat SKUs

I have about 30k products that I auto imported to WooCommerce from an API. There was a bug with the API that ended up adding duplicate products for most of the items, but now I am left with 30k+ products instead of 15k products.

There is no way I can go through and delete every duplicate manually, I have been trying to come up with a SQL script that could do this but due to all of the different data stored in WordPress it makes it complex.

It seems like this script gets all the unique rows, but not the duplicates.

SELECT meta_value, meta_key, count(*) 
FROM wp_postmeta WHERE meta_key LIKE '_sku'  
GROUP BY `meta_value`

So what I am looking to do is to find all rows with the column of meta_key = _skuwith duplicate rows of the value in the column called meta_value

So for example if I have some rows like this:

meta_id     post_id    meta_key    meta_value
1504098     57049      _sku        26785030612
1504135     57051      _sku        26785030612
1503993     57045      _sku        26785033309

I want it to look like this:

meta_id     post_id    meta_key    meta_value
1504135     57051      _sku        26785030612
1503993     57045      _sku        26785033309

So I need to delete the first row just based on the value in meta_value.

Upvotes: 4

Views: 5878

Answers (4)

PrashantFreshonly
PrashantFreshonly

Reputation: 11

Recently I resolved delete duplicate SKUs records with multiple permalinks ended with -2 or any digit at end containing duplicate URLs for same product Issue for WordPress site.

Query to get all duplicate SKUs and their all duplicate URLs / permalinks:

SELECT a.post_name,a.post_title,a.ID,b.meta_value as _sku FROM wp_posts a
INNER JOIN (SELECT meta_value,max(post_id) AS post_id FROM wp_postmeta WHERE meta_key='_sku' 
GROUP BY meta_value HAVING COUNT(meta_value) > 1 ) b ON a.ID=b.post_id WHERE post_type = 'product' AND   post_status = 'publish';

Query to Delete all duplicate SKU's and their all duplicate URLs / permalinks:

DELETE FROM wp_posts WHERE  id IN 
(
    SELECT a.post_id FROM
        (SELECT meta_value,max(post_id) AS post_id FROM wp_postmeta 
        WHERE meta_key='_sku' 
        GROUP BY meta_value HAVING COUNT(meta_value) > 1 ) a
        WHERE post_type = 'product' 
        AND post_status = 'publish' 
 );

Upvotes: 1

Hamaiz Ahmad
Hamaiz Ahmad

Reputation: 39

in my Case, I need to delete all products having duplicate SKU value so I ran this query 4 to 5 times and my problem was solved

DELETE FROM e78_posts WHERE id IN (SELECT a.post_id FROM ( SELECT MIN(meta_id) AS `meta_id` ,MIN(post_id) AS `post_id` FROM e78_postmeta WHERE meta_key='_sku' GROUP BY meta_value HAVING COUNT(*) > 1 ) a )
DELETE FROM e78_postmeta WHERE meta_id IN (SELECT a.meta_id FROM ( SELECT MIN(meta_id) AS `meta_id` ,MIN(post_id) AS `post_id` FROM e78_postmeta WHERE meta_key='_sku' GROUP BY meta_value HAVING COUNT(*) > 1 ) a )

Upvotes: 0

walta
walta

Reputation: 3466

I just solved this problem for a friend. We were trying to delete duplicate SKUs for a WordPress site that is using the WooCommerce plugin.

Here is a query to find all duplicate SKUs and their ids:

# CHECK QUERY
# Use this query to get a list of post_ids and old and new skus... 
# we wanted to keep the latest sku so we wanted to delete
# the older duplicate meta records

select * from (
 select post_id, min(meta_id) as deletethis, max(meta_id) as keepthis, meta_value, meta_key, count(*) as c from wp_postmeta 
    where meta_key like '_sku'
    and meta_value is not null
    and meta_value != ''
    group by `meta_value`
) as skus where c > 1

Here is a query to delete the duplicate SKU ids. We wanted the query to delete the duplicates so that any non-sku related meta entries were un-affected.

# DELETE QUERY
# Use this query to delete the older sku record and keep
# the newer sku record

delete from wp_postmeta where meta_id in (
select skus.deletethis from (
 select min(meta_id) as deletethis, max(meta_id) as keepthis, meta_value, meta_key, count(*) as c from wp_postmeta 
    where meta_key like '_sku'
    and meta_value is not null
    and meta_value != ''
    group by `meta_value`
) as skus where c > 1
);

Upvotes: 5

Darshan Mehta
Darshan Mehta

Reputation: 30819

If meta_id is unique then you can use the following query to SELECT all the (minimum) meta_id values for duplicate meta_value, e.g.:

SELECT MIN(meta_id)
FROM wp_postmeta 
GROUP BY meta_value
HAVING COUNT(*) > 1;

You can then wrap this query into DELETE to delete the rows, e.g.:

DELETE FROM wp_postmeta
WHERE meta_id IN (

SELECT a.meta_id FROM (
        SELECT MIN(meta_id) AS `meta_id`
        FROM wp_postmeta 
        GROUP BY meta_value
        HAVING COUNT(*) > 1
       ) a
);

Upvotes: 3

Related Questions