CODEWITHSUNDEEP
mysql
Matt Votsikas McLean
Matt Votsikas McLean

Reputation: 884

Delete duplicates where field1 and field2 are the identical

I have a table like

productId retailerId
1 2
1 2
1 4
1 6
1 8
1 8
2 3
2 6
2 6

Now, I need to remove the duplicates. I've figured out how to remove duplicates when one field is the same. But I need to remove the duplicates such as 1 2, 1 8 and 2 6, where both fields are identical.

Any help would be very gratefully received.

Upvotes: 2

Views: 87

Answers (1)

Bohemian
Bohemian

Reputation: 424983

Use mysql's multiple-table DELETE syntax as follows:

delete mytable
from mytable
join mytable t
on t.productId = mytable.productId
and t.retailerId = mytable.retailerId
and t.id < mytable.id

See this running on SQLFiddle.

Note that I have assumed that you have an id column as well.

Edit:

Since there is no id column, there simplest approach is to copy the desired data to a temporary table, delete all data, then copy it back, as follows:

CREATE TEMPORARY TABLE temptable
SELECT DISTINCT productId, retailerId
FROM mytable;
DELEYE FROM mytable;
INSERT INTO mytable
SELECT *
FROM temptable;

Upvotes: 2

Related Questions