Remove duplicate rows in MySQL

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
    Summary: in this tutorial, you are going to learn about MySQL View. We will explain how MySQL implements views. MySQL supports database views or views since version 5.X. In MySQL, almost features of views conform to the SQL: 2003 standard. MySQL process queries to the views in two ways: MySQL creates a temporary table…
    Tags: mysql, query

Leave a Reply

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

%d bloggers like this: