1

Possible Duplicate:
Removing duplicate rows from table in Oracle

how to remove the duplicate in table

Community
  • 1
  • 1
kmraju007
  • 11
  • 1
  • 1
    You will have to be a little bit more clear on this. What database are you using, what table structure do you have, what defines a record to be duplicate? – Adriaan Stander Mar 18 '11 at 05:57

2 Answers2

2

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
)
Jakob Alexander Eichler
  • 2,988
  • 3
  • 33
  • 49
  • isn't this turning out to be one big complex query? How about the running time/space complexing of this approach? – pr4n Jun 17 '11 at 09:46
1

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 )

user617597
  • 788
  • 3
  • 9
  • 21