set feedback off
create table T (col1 varchar2(5), col2 varchar2(2));
insert into T values ('a','a');
insert into T values ('a','a');
insert into T values ('a','z');
insert into T values ('a','z');
insert into T values ('a',null
);
insert into T values ('a',null
);
insert into T values ('m','q');
insert into T values ('m','q');
insert into T values ('m',null
);
insert into T values ('s','c');
insert into T values ('s','c');
insert into T values ('s','c');
insert into T values ('s','x');
insert into T values ('v','u');
delete from T t1
where t1.rowid
>
( select min(t2.rowID) from T t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2);
select * from t;
drop table t;
//================================
It's easy to introduce duplicate rows of data into Oracle tables by
running a data load twice without the primary key or unique indexes
created or enabled.
Here's how you remove the duplicate rows before the primary key or unique indexes can be created:
DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;
Here column1, column2, column3 constitute the identifying key for each record.
Be sure to replace our_table
with the table name from which
you want to remove the duplicate rows. The GROUP BY is used on the
columns that make the primary key for the table. This script deletes
each row in the group after the first row.
分享到:
相关推荐
duplicate绝对干货。利用duplicate复制数据库,文档中包换每一步的试验步骤,详细说明了每一步的作用及用途,和注意事项,一步一步至试验成功,绝对一次成功。
Oracle SQL Developer, v1.5.0.54.40 Release Notes 完整版下载:http://www.oracle.com/technology/global/cn/software/products/sql/index.html 1. Known Issues 1.1 General - Print prints only one page ...
某公司实施文档记录,非常珍贵的资料,通过duplicate实现oracle dataguard环境的搭建,需要的请下载。
Oracle RMAN DUPLICATE教程
Oracle Rman Active Database Duplicate 迁移方案V1.1.pdf
Oracle 11gR2 使用 RMAN duplicate from active database 复制数据库
Different strategies for removing duplicate records in SQL Server
This document will cover the process to duplicate an Oracle 10g database on another server using a Galaxy backup. This document should in no way be treated as the “End All” procedure. It is merely ...
Oracle 11g RAC-RAC DG Duplicate搭建(生产操作文档)Oracle 11g RAC-RAC DG Duplicate搭建(生产操作文档)
帮助你删除目录中重复的文件,很有用的小工具。
SQL删除重复列
Oracle 11gR2 dataguard搭建说明
RMAN高级应用之Duplicate复制数据库
Searching for Duplicate Records in Your Database491 总结491 校练场492 练习492 第三周回顾494 附件A 在SQL 中的常见术语495 ALTER DATABASE495 ALTER USER495 BEGIN TRANSACTION 495 CLOSE CURSOR495 COMMIT ...
Oracle DataGuard搭建通过duplicate
删除文件夹下相同的文件 博客: 【python中级】 基于md5值的图片去重工具 https://blog.csdn.net/jn10010537/article/details/118784182
14 ADG ORACLE 11G DATAGUARD 搭建(RMAN duplicate方式-相同目录结构) 15 ADG oracle 11g rac+单机dataguard实施文档(详细) 16 ADG Oracle 11g:ORACLE ACTIVE DATA GUARD 17 ADG Oracle 11gR2 使用copy 数据...
On a qualified select, update, or delete, the correct leaf page will be the lowest page of the tree in which one or more rows with the specified key or keys reside. A qualified operation is one that ...
FirmTools Duplicate Photo Finder 相似图像查询软件 你电脑中如果有很多图像,有很多可能是一个logo只差,你用其他md5检测软件是不能快速找出来的。这个软件可以搜索相似的图像,你查看后选择删除,非常的方便。 ...
Fast Duplicate File Finder