Shulou(Shulou.com)05/31 Report--

Editor to share with you oracle how to delete duplicate data to retain the first record, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Oracle removes duplicate data and retains the first record

1. Look up the redundant duplicate records in the table. Duplicate records are judged by a single field (Id).

Select * from table where Id in (select Id from table group byId having count (Id) > 1)

2. Delete redundant duplicate records from the table. Duplicate records are judged according to a single field (Id), leaving only the records with the smallest rowid.

DELETE from table WHERE (id) IN (SELECT id FROM table GROUP BY id HAVING COUNT (id) > 1) AND ROWID NOT IN (SELECT MIN (ROWID) FROM table GROUP BY id HAVING COUNT (*) > 1)

3. Look up redundant duplicate records in the table (multiple fields)

Select * from table a where (a.Idmema.seq) in (select Id,seq from table group by Id,seq having count (*) > 1)

4. Delete the redundant duplicate records (multiple fields) in the table, leaving only the record with the smallest rowid

Delete from table a where (a.Idmema.seq) in (select Id,seq from table group by Id,seq having count (*) > 1) and rowid not in (select min (rowid) from table group by Id,seq having count (*) > 1)

5. Look up the redundant duplicate records in the table (multiple fields), excluding the records with the smallest rowid

Select * from table a where (a.Idmema.seq) in (select Id,seq from table group by Id,seq having count (*) > 1) and rowid not in (select min (rowid) from table group by Id,seq having count (*) > 1) these are all the contents of the article "how to delete duplicate data and keep the first record in oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.