removing duplicate in table [duplicate]

This question already has answers here: 开发者_如何学编程 Closed 11 years ago.

Possible Duplicate:

Removing duplicate rows from table in Oracle

how to remove the duplicate in table

---

If you do not like doublicate values in the database, you maybe should set an unique index.

I have tested a complicated query for you which removes all dublicates in the table test which have an equal name. The one with the lowest id will not be removed.

You have to create these derived tables (SELECT * FROM test) because usually it is forbidden to do a select in the update table.

enjoy it.

DELETE FROM `test` WHERE `id` IN (
SELECT t1.`id`
FROM (

SELECT *
FROM `test`
) AS `t1`
LEFT JOIN (

SELECT *
FROM `test`
) AS `t2` ON t2.`name` = t1.`name`
AND t2.`id` < t1.`id`
GROUP BY t1.`id`
HAVING count( t2.`id` ) >0
)

---

http://www.kodyaz.com/articles/delete-duplicate-records-rows-in-a-table.aspx

By Assuming,tablesample(firstname,lastname,id)

DELETE FROM Users WHERE Id IN ( SELECT Users.Id FROM Users INNER JOIN Dublicates_CTE ON Users.FirstName = Dublicates_CTE.FirstName AND Users.LastName = Dublicates_CTE.LastName AND Users.Id <> Dublicates_CTE.Id )