CODEWITHSUNDEEP
mysqlsql
Miguel E
Miguel E

Reputation: 1326

Delete partially similar rows in MySQL

I need to delete partially similar rows in a table, using MySQL. Ex:

From table1 (id,color1,color2,key) content:

id,color1,color2,key
-----------------------------
1,Blue,Green,AASDDD
2,Blue,Green,JJUUYYY
3,Blue,Red,HHYYY
4,Green,Red,KKIII
5,Blue,Red,KKIIUUUU

I'd like to delete the duplicate rows in color1,color2 and get:

id,color1,color2,key
-----------------------------
1,Blue,Green,AASDDD
3,Blue,Red,HHYYY
4,Green,Red,KKIII

Something like

delete FROM table1 WHERE Exists(SELECT color1,color2 FROM table1)

What's the best way to do this in MySQL without creating a temporary table?

(I know there are many posts about deleting duplicate rows in MySQL, but not for partially matching rows.)

Upvotes: 1

Views: 322

Answers (3)

user1532340
user1532340

Reputation: 11

   DELETE FROM table1 
   WHERE id NOT IN (SELECT MIN(id) FROM table1 GROUP BY colo1, color2)

Upvotes: 0

rogal111
rogal111

Reputation: 5933

DELETE FROM table1 WHERE id NOT IN (SELECT min(id) FROM table1 GROUP BY color1, color2)

Here you've manual for GROUP BY: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions-and-modifiers.html

Tutorials:

NOTE:

In MySQL:

Currently, you cannot delete from a table and select from the same table in a subquery.

At least for 5.0 version. ( http://dev.mysql.com/doc/refman/5.0/en/delete.html )

So in mysql you may use TEMPORARY TABLE in transaction:

BEGIN;
CREATE TEMPORARY TABLE colors_temp SELECT min(id) as id FROM table1 GROUP BY color1, color2;
DELETE FROM table1 WHERE id NOT IN (SELECT id FROM colors_temp);
DROP TABLE colors_temp;
COMMIT;

Upvotes: 3

juergen d
juergen d

Reputation: 204766

DELETE FROM table1 
WHERE id NOT IN (SELECT min(id) 
                 FROM table1 
                 GROUP BY color1, color2)

edit

MySQL can't delete from a table with a subquery of the same table. To work around that limitation you can do this:

DELETE FROM table1 
WHERE id NOT IN (select * from (SELECT min(id) 
                               FROM table1 
                               GROUP BY color1, color2) t)

Upvotes: 8

Related Questions