Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Find and remove duplicates

Find and remove duplicates

Author: rootsql Date: 7-11-2011, 22:31

Find and remove duplicates.

CREATE TABLE mytable(
  rowid INT(11) NOT NULL,
  msgid INT(11) DEFAULT NULL,
  userid INT(11) DEFAULT NULL,
  PRIMARY KEY (rowid)
);
INSERT INTO mytable VALUES 
  (1, 5, 33),
  (2, 5, 12),
  (3, 4, 21),
  (4, 5, 33),
  (5, 5, 33),
  (6, 4, 15),
  (7, 4, 21);

 

Select duplicates:

SELECT t1.* FROM mytable t1
  JOIN (
        SELECT msgid, userid, MIN(rowid) min_rowid FROM mytable
          GROUP BY msgid, userid
        ) t2
  ON t1.rowid <> t2.min_rowid AND t1.msgid = t2.msgid AND t1.userid = t2.userid;
+-------+-------+--------+
| rowid | msgid | userid |
+-------+-------+--------+
|     4 |     5 |     33 |
|     5 |     5 |     33 |
|     7 |     4 |     21 |
+-------+-------+--------+

 

Remove duplicates with a DELETE statement:

DELETE t1 FROM mytable t1
  JOIN (
        SELECT msgid, userid, MIN(rowid) min_rowid FROM mytable
          GROUP BY msgid, userid
        ) t2
  ON t1.rowid <> t2.min_rowid AND t1.msgid = t2.msgid AND t1.userid = t2.userid;

Remove duplicates with a new unique key:

ALTER IGNORE TABLE mytable
  ADD UNIQUE KEY(msgid, userid);

 

This ALTER TABLE with IGNORE keyword will create new unique key and remove all duplicates from the table in a one step.

SELECT * FROM mytable;
+-------+-------+--------+
| rowid | msgid | userid |
+-------+-------+--------+
|     1 |     5 |     33 |
|     2 |     5 |     12 |
|     3 |     4 |     21 |
|     6 |     4 |     15 |
+-------+-------+--------+
Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
Information
Members of Guests cannot leave comments.