Remove duplicate rows in MySQL

Rate this post

Easiest way to do this is to add a UNIQUE index on the column. When you write the ALTER statement, include the IGNORE keyword. Like so:

ALTER IGNORE TABLE jobs
 ADD UNIQUE INDEX idx_name (site_id, title, company);

This will drop all the duplicate rows. As an added benefit, future INSERTs that are duplicates will error out. As always, you may want to take a backup before running something like this…

Related Posts

  • 80
    ALTER IGNORE TABLE `cdr` ADD UNIQUE(`uniqueid`)      
    Tags: table, add, ignore, duplicate, mysql, alter, remove, rows
  • 44
    Normally MySQL replication will stop whenever there is an error running a query on the slave. This happens in order for us to be able to identify the problem and fix it, and keep the data consistent with the mater that has sent the query. You can skip such errors, even if…
    Tags: query, error, duplicate, mysql, will
  • 43
    I connect to mysql from my Linux shell. Every now and then I run a SELECT query that is too big. It prints and prints and I already know this is not what I meant. I would like to stop the query. Hitting Ctrl+C (a couple of times) kills mysql…
    Tags: mysql, query, running
  • 40
    To enable the query log, put this in /etc/my.cnf in the [mysqld] section log = /path/to/query.log #works for mysql < 5.1.29 Also, to enable it from MySQL console SET general_log = 1; Remember that this logfile can grow very big on a busy server. update: With mysql 5.1.29+ , the log option is deprecated. To specify…
    Tags: mysql, table, query
  • 38
    Very often we need to extract last 1 month, 2 months data from mysql DB. Here we will show easy way to get the data of given interval. SELECT * from testtable WHERE startdate BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND DATE_SUB(NOW(), INTERVAL 2 MONTH) The above query will return records whose order…
    Tags: mysql, will, query

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: