Delete rows with duplicate column value in MySQL

[Marius]

I have a table with the following columns: id | url | title | company .
Now, I need to delete rows having same url and title.
I want to know if this can be done only using SQL query.

An easy way to do this is to add a UNIQUE index on the columns with duplicate values, using the ALTER statement.
When you write the ALTER statement, include the IGNORE keyword. Like so:

Code: Select all

ALTER IGNORE TABLE table_name ADD UNIQUE INDEX idx_name (url, title);

This will drop all the duplicate rows. The future INSERTs that are duplicates will error out.

Another solution, if you don't want to alter the column properties then you can use the query below.

Code: Select all

DELETE FROM table_name
WHERE id NOT IN (
  SELECT * FROM (SELECT MIN(n.id) FROM table_name n GROUP BY n.url, n.title) x
)

Or this:

Code: Select all

DELETE a FROM table_name AS a, table_name AS b
WHERE
  (a.url = b.url OR a.url IS NULL AND b.url IS NULL)
  AND (a.title = b.title OR a.title IS NULL AND b.title IS NULL)
  AND b.ID > a.ID;

- It is better to make a backup of the table before applying one of these queries.