Page 1 of 1
Remove duplicate products using mysql query
Posted: Sat May 30, 2015 11:17 pm
by magzen
I have many duplicate products (5000-8000), takes time to go through and delete by hand.
Is there a query to delete these duplicates and keep 1 of each product?
The duplicates have unique SKU & Model if that helps.
Iam using Opencart 2.0.20
/magzen
Re: Remove duplicate products using mysql query
Posted: Sun May 31, 2015 1:56 am
by chulcha
1. backup
2. backup
3,
create temporary table tmptable (id int);
INSERT tmptable (id) select product_id
from oc_product p
where exists (
select *
from oc_product p1
where p1.model = p.model
and p1.sku = p.sku
and p1.product_id > p.product_id
);
delete
from oc_product
where product_id in (select id from tmptable);
Re: Remove duplicate products using mysql query
Posted: Tue Jun 02, 2015 2:54 am
by chulcha
continue
delete
from oc_product_description
where product_id in (select id from tmptable);
Re: Remove duplicate products using mysql query
Posted: Thu Jun 04, 2015 7:56 am
by magzen
chulcha wrote:1. backup
2. backup
3,
create temporary table tmptable (id int);
INSERT tmptable (id) select product_id
from oc_product p
where exists (
select *
from oc_product p1
where p1.model = p.model
and p1.sku = p.sku
and p1.product_id > p.product_id
);
delete
from oc_product
where product_id in (select id from tmptable);
thanks will try it out
Re: Remove duplicate products using mysql query
Posted: Fri Jun 12, 2015 9:47 pm
by magzen
worked perfectly, removed 7000 duplicates
Re: [SOLVED]Remove duplicate products using mysql query
Posted: Tue Aug 25, 2015 9:48 pm
by ClosedCart
what do I do here exactly?
Re: [SOLVED]Remove duplicate products using mysql query
Posted: Wed Aug 26, 2015 1:58 am
by chulcha
Code: Select all
INSERT tmptable (id) select product_id
from oc_product p
where exists (
select *
from oc_product p1
where p1.model = p.model
and p1.sku = p.sku
and p1.product_id > p.product_id
);
delete from oc_product where product_id in (select id from tmptable);
DELETE FROM oc_product WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_attribute WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_description WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_discount WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_image WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_option WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_option_value WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_related WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_related WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_reward WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_special WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_tag WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_to_category WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_to_download WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_to_layout WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_to_store WHERE product_id in (select id from tmptable);
DELETE FROM oc_review WHERE product_id in (select id from tmptable);
Re: [SOLVED]Remove duplicate products using mysql query
Posted: Wed Aug 26, 2015 3:03 am
by ClosedCart
Hey thanks I thought you'd never see this again since it's been three months. I put in what you said into my SQL box and ran it and it said:
edit:
I'm running 2.0.3.1 if that matters?
Re: [SOLVED]Remove duplicate products using mysql query
Posted: Wed Aug 26, 2015 3:48 pm
by chulcha
All queries you must do in a SQL window together
Re: [SOLVED]Remove duplicate products using mysql query
Posted: Fri Sep 30, 2016 11:41 pm
by vipvicks71
What about removing linked tables:
oc_product_description,
oc_product_image,
oc_product_option,
oc_product_option_value,
oc_product_to_category,
oc_product_to_store
How do we remove from these too?
Re: [SOLVED]Remove duplicate products using mysql query
Posted: Sat Oct 01, 2016 6:03 am
by chulcha
chulcha wrote:Code: Select all
INSERT tmptable (id) select product_id
from oc_product p
where exists (
select *
from oc_product p1
where p1.model = p.model
and p1.sku = p.sku
and p1.product_id > p.product_id
);
delete from oc_product where product_id in (select id from tmptable);
DELETE FROM oc_product WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_attribute WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_description WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_discount WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_image WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_option WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_option_value WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_related WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_related WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_reward WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_special WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_tag WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_to_category WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_to_download WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_to_layout WHERE product_id in (select id from tmptable);
DELETE FROM oc_product_to_store WHERE product_id in (select id from tmptable);
DELETE FROM oc_review WHERE product_id in (select id from tmptable);
Re: [SOLVED]Remove duplicate products using mysql query
Posted: Sun Dec 04, 2016 1:03 pm
by magzen
Hello,
Long time ago i made this query but noticed it was updated afterwards to remove all duplicates from all ids like options, attribute store category etc..
i only runned the following query
Code: Select all
INSERT tmptable (id) select product_id
from oc_product p
where exists (
select *
from oc_product p1
where p1.model = p.model
and p1.sku = p.sku
and p1.product_id > p.product_id
);
is it possible to somehow run the rest of the query afterwards to correct this issue? because when iam exporting the products in csv exporter iam getting several error messages like the following
[6:0:9] WARNING: Product option Color (type = image) has an empty list of product option values. Skipped.
[6:0:9] WARNING: Product option Color (type = image) has an empty list of product option values. Skipped.
[6:0:9] WARNING: Product option Color (type = image) has an empty list of product option values. Skipped.
[6:0:9] WARNING: Product option Color (type = image) has an empty list of product option values. Skipped.
[6:0:9] WARNING: Product option Type (type = select) has an empty list of product option values. Skipped.
and if i try to add a new color in options many are deleted right away from database which makes many colors go missing from products
hope this can be fixed
Thanks
Re: Remove duplicate products using mysql query
Posted: Sun Dec 11, 2016 11:53 am
by magzen
anyone knows if this is fixable?