Here is what my table looks like
=======================================
|| id | collection_id | product_id | price ||
=======================================
|| 1 | 1 | 1 | 2.50 ||
|| 2 | 1 | 1 | 2.60 ||
|| 3 | 1 | 1 | 2.40 ||
|| 4 | 2 | 1 | 2.50 ||
=======================================
Here is a rough outline of what I want to achieve; using some pseudo code.
What I want to do is DELETE FROM prices WHERE (collection_ id && product_ id are duplicated) AND WHERE (id < highest duplicated row)
SO what I want to do in plain English is delete rows where the id is equal to 1 & 2 but keep the row where the id is equal to 3 because this the newest.
A simple DELETE JOIN
should do it; delete all rows where there exists at least one row with a matching collection_id
and product_id
and a greater value of id
;
DELETE p1
FROM prices p1
JOIN prices p2
ON p1.collection_id = p2.collection_id
AND p1.product_id = p2.product_id
AND p1.id < p2.id
An SQLfiddle to test with, and remember to always back up your data before running updates or deletes from random people on the Internet.