Oracle de duplicated data

Riveore 2021-11-25 18:44:27
oracle duplicated data



There are two types of duplicate data , One is the repetition of some fields in the table , One is that more than two lines of records are exactly the same .


  • Repetition of some fields :

Query non duplicate data SQL:
select Field 1, Field 2,count(*) from Table name group by Field 1, Field 2 having count(*) = 1

Deletion of duplicate data in some fields :

Query duplicate data SQL

select Field 1, Field 2,count(*) from Table name group by Field 1, Field 2 having count(*) > 1

Delete the duplicate data found above :

delete from Table name a where Field 1, Field 2 in
    (select Field 1, Field 2,count(*) from Table name group by Field 1, Field 2 having count(*) > 1)

The above is to delete the queried data , This deletion is inefficient , When the amount of data is large , Not suitable for .

Another method is to insert the duplicate data found in the query into a temporary table , Then delete it , such , When deleting, you don't have to query again .

CREATE TABLE A temporary table AS
(select Field 1, Field 2,count(*) from Table name group by Field 1, Field 2 having count(*) > 1)

-- Create temporary tables , And insert the query data into it .
Delete again :
delete from Table name a where Field 1, Field 2 in (select Field 1, Field 2 from A temporary table );
This operation of creating a temporary table first and then deleting it is much more efficient than deleting it directly with one statement .

The above is to delete all duplicate data , One of the records that does not retain duplicate data .

How to keep one record of duplicate data ?

stay oracle in , A hidden field is rowid, Each record in the table has a unique rowid,
If you want to keep the latest record , You can use this field , Keep duplicate data rowid The biggest record is enough

In the following brackets are the duplicate data found in the query rowid The biggest record , Outside the brackets is the query except rowid Duplicate data other than the largest :

select a.rowid,a.* from Table name a
where a.rowid !=
(
   select max(b.rowid) from Table name b
   where a. Field 1 = b. Field 1 and
   a. Field 2 = b. Field 2
)


To remove duplicate data , Keep only the latest piece of data :
delete from Table name a
where a.rowid !=
(
   select max(b.rowid) from Table name b
   where a. Field 1 = b. Field 1 and
   a. Field 2 = b. Field 2
)

The above is to delete the duplicate data found in the query , Keep the latest record , This deletion is inefficient , When the amount of data is large , Not suitable for .

Consider creating temporary tables , You will need to determine the duplicate fields ,rowid Insert into temporary table , Then compare when deleting .

create table A temporary table as
    select a. Field 1,a. Field 2,MAX(a.ROWID) dataid from Official form a GROUP BY a. Field 1,a. Field 2;

delete from Table name a
where a.rowid !=
(
   select b.dataid from A temporary table b
   where a. Field 1 = b. Field 1 and
   a. Field 2 = b. Field 2
);


  • Deletion of complete duplicate records :

When two or more rows of records in a table are exactly the same , You can use the following statement to get the record after removing duplicate data :

select distinct * from Table name

Put the queried records into the temporary table , Then delete the original table record , Finally, import the data of the temporary table back to the original table .

CREATE TABLE A temporary table AS (select distinct * from Table name );
truncate table Official form ;
insert into Official form (select * from A temporary table );
drop table A temporary table ;
版权声明
本文为[Riveore]所创,转载请带上原文链接,感谢
https://javamana.com/2021/11/20211109102332581h.html

  1. GitHub上霸榜久居不下的《Java面试突击宝典》,java图形用户界面设计基础
  2. GitHub上访问下载破百万的神仙文档《Java面试神技》看完我呆了,java面试问项目中遇到的问题
  3. GitHub上标星75k 超牛的《Java面试突击版,java高级工程师技能
  4. Docker development environment Preview
  5. JavaScript高級深入淺出:掌握 this 指向
  6. JavaScript Advanced Insight and outside: Mastering this direction
  7. Vue de l'application pratique de Javascript, drop drag Event
  8. docker 安装部署 Jenkins 2.322
  9. kafka安装
  10. 近九万字图文详解RabbitMQ
  11. Engaged in Java for one and a half years, how to break through yourself
  12. 输出9*9乘法表----java
  13. 判断一个数是不是素数-------java
  14. java项目,记录页面修改值,内部打“官司”用
  15. Docker installation Deployment Jenkins 2.322
  16. Comment porter un pantalon en hiver? Les petits hommes, les jambes épaisses et la largeur de l'entrejambe peuvent être vus. 3 techniques pour éviter la foudre
  17. MySQL下载和安装教程
  18. In depth analysis of rocketmq source code - message storage module
  19. Spring transaction management
  20. mysql恢复ibd数据,为何频频报错?
  21. [skills with annual salary of 60W] after working for 5 years, do you really understand netty and why to use it? (deep dry goods)
  22. Pourquoi MySQL récupère - t - il fréquemment les données ibd?
  23. Tutoriels de téléchargement et d'installation MySQL
  24. In IntelliJ idea, develop a fishing and reading plug-in
  25. Talk about how to integrate SPI with spring
  26. 重学Spring系列之Swagger2.0和Swagger3.0
  27. Hadoop05【命令操作】,Java400道面试题通关宝典助你进大厂
  28. Guava Cache缓存设计原理,java基础入门
  29. Gson:GitHub 标星 18K 的 JSON 解析器,Google 出品的 Java JSON 解析器
  30. gRPC学习之六:gRPC-Gateway集成swagger,java语言程序设计与数据结构进阶版
  31. Projet Java, enregistrer la valeur de modification de la page, utilisé pour les litiges internes
  32. Déterminer si un nombre est un nombre premier - - Java
  33. Sortie 9 * 9 tableau de multiplication - Java
  34. Plusieurs façons de réinitialiser le déplacement du Groupe de consommateurs Kafka
  35. GRPC Learning six: GRPC Gateway Integrated Swagger, Java language programming and data structure Advanced Edition
  36. python数据结构:数组、列表、栈、队列及实现
  37. Gson: json Parser for github Star 18k, Java json Parser from Google
  38. hive学习笔记之二:复杂数据类型,五分钟搞懂MySQL主从复制原理
  39. hive学习笔记之一:基本数据类型,java实现分页技术详解
  40. HDU-3038-How Many Answers Are Wrong【 带权并查集 】题解,java实战项目论坛
  41. HDU 1078 FatMouse and Cheese(记忆化搜索,DP,rocketmq教程教程
  42. Principes de conception du cache Guava, Introduction à la base Java
  43. Un pantalon beige = élégant, blanc + haut de gamme, si beau cet hiver!
  44. Hadoop05 【 commande 】, Java 400 questions d'entrevue pour vous aider à entrer dans l'usine
  45. [Java multithreading] static synchronized method and synchronized (class) code block
  46. [Java multithreading] synchronized statement block
  47. [Java multithreading] synchronized synchronization method
  48. [Java multithreading] thread priority and guard thread
  49. [Java multithreading] stop the thread
  50. Several methods of [Java multithreading] thread
  51. [Java multithreading] Java multithreading skills
  52. Structure des données Python: tableaux, listes, piles, files d'attente et implémentations
  53. Re - Learning Swagger 2.0 and Swagger 3.0 of Spring Series
  54. Error editing HTML5 using webstorm
  55. 【Spring】mybatis-spring
  56. JavaScript post request returned an exception, possibly Cross - Domain request
  57. Springboot + mybatis Plus + springsecurity + JWT implements user stateless request authentication (front and back end separation)
  58. SQL injection and XSS attack of springboot security vulnerability
  59. Docker employee readme: Why did docker lose to kubernetes?
  60. 19 high force linux commands, hurry to use them!