Mysql Tricks : Remove duplicate entries

Remove duplicate entries.  Assume the following table and data.

              CREATE TABLE IF NOT EXISTS dupTest (
                pkey int(11) NOT NULL auto_increment,
                a int,
                b int,
                c int,
                timeEnter timestamp(14),
               PRIMARY KEY  (pkey)

               );

               insert into dupTest (a,b,c) values (1,2,3),(1,2,3),
                  (1,5,4),(1,6,4);

       mysql> select * from dupTest;
       select * from dupTest;
       +------+------+------+------+---------------------+
       | pkey | a    | b    | c    | timeEnter           |
       +------+------+------+------+---------------------+
       |    1 |    1 |    2 |    3 | 2004-04-16 10:55:35 |
       |    2 |    1 |    2 |    3 | 2004-04-16 10:55:35 |
       |    3 |    1 |    5 |    4 | 2004-04-16 10:55:35 |
       |    4 |    1 |    6 |    4 | 2004-04-16 10:55:35 |
       +------+------+------+------+---------------------+
       4 rows in set (0.00 sec)

       mysql>

       Note, the first two rows contains duplicates in columns a and b. It contains
       other duplicates; but, leaves the other duplicates alone.

          mysql> ALTER IGNORE TABLE  dupTest ADD UNIQUE INDEX(a,b);

          mysql> select * from dupTest;
          select * from dupTest;
          +------+------+------+------+---------------------+
          | pkey | a    | b    | c    | timeEnter           |
          +------+------+------+------+---------------------+
          |    1 |    1 |    2 |    3 | 2004-04-16 11:11:42 |
          |    3 |    1 |    5 |    4 | 2004-04-16 11:11:42 |
          |    4 |    1 |    6 |    4 | 2004-04-16 11:11:42 |
          +------+------+------+------+---------------------+
          3 rows in set (0.00 sec)

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Leave a Reply

*
To prove that you're not a bot, enter this code
Anti-Spam Image