How to delete duplicates on a MySQL table?

mysql duplicates
Ali Demirci picture Ali Demirci · Apr 13, 2010 · Viewed 218.9k times · Source

I need to DELETE duplicated rows for specified sid on a MySQL table.

How can I do this with an SQL query?

DELETE (DUPLICATED TITLES) FROM table WHERE SID = "1"

Something like this, but I don't know how to do it.

Answer

user187291 picture user187291 · Apr 13, 2010

this removes duplicates in place, without making a new table

ALTER IGNORE TABLE `table_name` ADD UNIQUE (title, SID)

note: only works well if index fits in memory

Related questions

Find duplicate records in MySQL

I want to pull out duplicate records in a MySQL Database. This can be done with: SELECT address, count(id) as cnt FROM list GROUP BY address HAVING cnt > 1 Which results in: 100 MAIN ST 2 I would like to pull …

Read More
Delete all Duplicate Rows except for One in MySQL?

How would I delete all duplicate data from a MySQL Table? For example, with the following data: SELECT * FROM names; +----+--------+ | id | name | +----+--------+ | 1 | google | | 2 | yahoo | | 3 | msn | | 4 | google | | 5 | google | | 6 | yahoo | +----+--------+ I would use SELECT DISTINCT name …

Read More
Remove duplicate rows in MySQL

I have a table with the following fields: id (Unique) url (Unique) title company site_id Now, I need to remove rows having same title, company and site_id. One way to do it will be using the following SQL …

Read More