This question has already been answered:

It is necessary to remove duplicates in the MySQL table, how it is done - I do not know. The table looks like this:

enter image description here

Lines 2018.

PS: This is not a duplicate of the question , because that solution does not fit, I need to remove duplicates only in the image column.

Reported as a duplicate by the participants Small , Alex , aleksandr barakin , user194374, Denis Bubnov 6 Dec '16 at 8:42 .

A similar question was asked earlier and an answer has already been received. If the answers provided are not exhaustive, please ask a new question .

  • This is a duplicate question. In my answer, I showed two SQL for particular cases: if duplicates need to count rows by one field or by two. - Shallow

2 answers 2

Since there is not much data, you can simply:

DELETE p1 FROM oc_product_image p1, oc_product_image p2 WHERE p1.product_image_id < p2.product_image_id AND p1.product_id = p2.product_id

There will be lines with maximum product_image_id

You can also SELECT DISTINCT'om make INSERT to a new empty table, it will work faster if the base is million.

  • Duck, I generally xs about the maximum product_image_id, it does not really interest me. I'm interested in lines with duplicate links to pictures. They must have the same id, because it is a table of photos in the OpenCart product cards. - Chutka Traveled
  • DELETE p1 FROM oc_product_image p1, oc_product_image p2 WHERE p1.image <p2.image AND p1.product_id = p2.product_id - Grigoryants Artem
  • Those. will this query remove duplicates only in the image column? Well, respectively, the line with these doubles? - Chutka Traveled
  • DELETE p1 FROM oc_product_image p1, oc_product_image p2 WHERE p1.product_image_id <p2.product_image_id AND p1.image = p2.image this one will remove lines with the same fields image but just in case make backup, but it’s not enough) - Grigoryants Artem
  • Prev request - left only duplicates: D - Chutka Traveled

You can simply execute the query:

 ALTER IGNORE TABLE oc_product_image ADD UNIQUE (product_id, image);

Not only that duplicates will be removed when it is executed (though which of the product_image_id will be deleted, and which will remain - unknown, but the vehicle says that it doesn’t matter), but such duplicates will not appear in the future either - the server simply will not allow it.

  • An important point - in 5.7.4 alter ignore table was removed, this method is no longer there. - Shallow
  • Thank you all very much, doubles sawed out New task Komrad. There is oc_producte_image and there is oc_product Aidishniki in both tables correspond, and links to pictures in oc_product are different, how to make the paths to pictures (image column) were the same in both tables skrinshoter.ru/s/041216/wzJgonUs
  • @ ChukkaPehavshiy new puzzles in StackOverflow are made out of new questions, and not comments. And if the solution of this question came up, tick this off and add a reply to the respondent. - artoodetoo