If any time, we want to delete duplicate records from table than we can use following query:
tableName - Our SQL table name
idtablename - Autoincremented Primary Key
columnname - By which we can identify the duplicate value in Table.
DELETE tablename WHERE idtablename in(
SELECT idtablename FROM tablename,(
SELECT columnname,MIN(idtablename) AS minID
FROM tablename
GROUP BY columnname
HAVING COUNT(*) >1) c
WHERE c.columnname = tablename.columnname
AND idtablename > minID)
tableName - Our SQL table name
idtablename - Autoincremented Primary Key
columnname - By which we can identify the duplicate value in Table.
DELETE tablename WHERE idtablename in(
SELECT idtablename FROM tablename,(
SELECT columnname,MIN(idtablename) AS minID
FROM tablename
GROUP BY columnname
HAVING COUNT(*) >1) c
WHERE c.columnname = tablename.columnname
AND idtablename > minID)
Good one..
ReplyDeleteAs well as i found one more solution
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)