Post by magzen » Sat May 30, 2015 11:17 pm

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
Last edited by magzen on Sun Dec 11, 2016 11:52 am, edited 2 times in total.

Active Member

Posts

Joined
Tue Jun 05, 2012 7:04 pm

Post by chulcha » Sun May 31, 2015 1:56 am

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);

Active Member

Posts

Joined
Fri Jul 18, 2014 4:39 pm

Post by chulcha » Tue Jun 02, 2015 2:54 am

continue

delete
from oc_product_description
where product_id in (select id from tmptable);

Active Member

Posts

Joined
Fri Jul 18, 2014 4:39 pm

Post by magzen » Thu Jun 04, 2015 7:56 am

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

Active Member

Posts

Joined
Tue Jun 05, 2012 7:04 pm

Post by magzen » Fri Jun 12, 2015 9:47 pm

worked perfectly, removed 7000 duplicates ;D

Active Member

Posts

Joined
Tue Jun 05, 2012 7:04 pm

Post by ClosedCart » Tue Aug 25, 2015 9:48 pm

what do I do here exactly?
Last edited by ClosedCart on Mon Dec 21, 2015 6:29 am, edited 1 time in total.

New member

Posts

Joined
Tue Jun 02, 2015 9:11 pm

Post by chulcha » Wed Aug 26, 2015 1:58 am

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);


Active Member

Posts

Joined
Fri Jul 18, 2014 4:39 pm

Post by ClosedCart » Wed Aug 26, 2015 3:03 am

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:

Image

edit:
I'm running 2.0.3.1 if that matters?
Last edited by ClosedCart on Mon Dec 21, 2015 6:28 am, edited 1 time in total.

New member

Posts

Joined
Tue Jun 02, 2015 9:11 pm

Post by chulcha » Wed Aug 26, 2015 3:48 pm

All queries you must do in a SQL window together

Active Member

Posts

Joined
Fri Jul 18, 2014 4:39 pm

Post by vipvicks71 » Fri Sep 30, 2016 11:41 pm

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?

Newbie

Posts

Joined
Tue Nov 03, 2015 4:30 am

Post by chulcha » Sat Oct 01, 2016 6:03 am

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);


Active Member

Posts

Joined
Fri Jul 18, 2014 4:39 pm

Post by magzen » Sun Dec 04, 2016 1:03 pm

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

Active Member

Posts

Joined
Tue Jun 05, 2012 7:04 pm

Post by magzen » Sun Dec 11, 2016 11:53 am

anyone knows if this is fixable?

Active Member

Posts

Joined
Tue Jun 05, 2012 7:04 pm
Who is online

Users browsing this forum: No registered users and 204 guests