how to delete duplicates on mysql table?

Question

hello I need to know any way to delete duplicated items for specified sid on table.

how can I do this with SQL queries ?

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

something like this but i don't know true one :/ thanks.

Answer 1

DELETE FROM table WHERE Field IN
(SELECT Field FROM table GROUP BY Field HAVING COUNT(Field) > 1)
WHERE SID = "1"

EDIT:

The query above would delete all duplicate records, to keep one of each:

DELETE FROM table WHERE id NOT IN

 (SELECT id FROM
     (SELECT id, field 
     FROM table WHERE SID = "1" 
     GROUP BY field 
     HAVING COUNT(field) > 1) 
 as tmp)    

AND field in

 (SELECT DISTINCT field 
 FROM table 
 WHERE SID = "1" 
 GROUP BY field 
 HAVING COUNT(field) > 1)

I couldn't fully test it but I think this works.

Answer 2

Article: Deleting Duplicate Rows in a MySQL Database

Answer 3

Following remove duplicates for all SID-s, not only single one.

With temp table

CREATE TABLE table_temp AS
SELECT * FROM table GROUP BY title, SID;

DROP TABLE table;
RENAME TABLE table_temp TO table;

Without temp table:

DELETE FROM `table` WHERE id IN (
  SELECT all_duplicates.id FROM (
    SELECT id FROM `table` WHERE (`title`, `SID`) IN (
      SELECT `title`, `SID` FROM `table` GROUP BY `title`, `SID` having count(*) > 1
    )
  ) AS all_duplicates 
  LEFT JOIN (
    SELECT id FROM `table` GROUP BY `title`, `SID` having count(*) > 1
  ) AS grouped_duplicates 
  ON all_duplicates.id = grouped_duplicates.id 
  WHERE grouped_duplicates.id IS NULL
)

Answer 4

this removes duplicates in place, without making a new table

ALTER IGNORE TABLE foobar ADD UNIQUE (title, SID)

note: only works well if index fits in memory

Answer 5

This procedure will remove all duplicates (incl multiples) in a table, keeping the last duplicate. This is an extension of Retrieving last record in each group

Hope this is useful to someone.

DROP TABLE IF EXISTS UniqueIDs;
CREATE Temporary table UniqueIDs (id Int(11));

INSERT INTO UniqueIDs
    (SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON
    (T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields 
    AND T1.ID < T2.ID)
    WHERE T2.ID IS NULL);

DELETE FROM Table WHERE id NOT IN (SELECT ID FROM UniqueIDs);