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
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);
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 outchulcha 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);
what do I do here exactly?
Last edited by ClosedCart on Mon Dec 21, 2015 6:29 am, edited 1 time in total.
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);
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?
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.
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?
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?
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);
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
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
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
);
[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
Who is online
Users browsing this forum: No registered users and 73 guests