How to use rowid to delete duplicate records
This article will explain in detail how to use rowid to delete duplicate records. Xiaobian thinks it is quite practical, so share it with you for reference. I hope you can gain something after reading this article.
First: When there is only one duplicate record:
In oracle, each record has a rowid, which is unique throughout the database and
Rowid identifies which data file, block, or row in oracle each record is on. In duplicate records,
The contents of all columns may be the same, but the rowid is not.
SQL> select * from tt;
NAME AGE ID
---------- ---------- ----
Zhao 33 1003
King 44 1004
Zhang San 20 1000
Li Si 26 1001
Zhang San 20 1002
SQL> insert into tt values ('',33,'1003');
1 line created.
SQL> commit;
Submission complete.
SQL> select rowid,tt.* from tt;
ROWID NAME AGE ID
------------------ ---------- ---------- ----
AAAR7 MAAEAAACzAAA Zhao 33 1003
AAAR7MAAEAAAAC1AAA Zhao 33 1003
AAAR7MAAEAAAAC1AAD King 44 1004
AAAR7MAAEAAAAC2AAA Zhang San 20 1000
AAAR7MAAEAAAAC2AAB Li Si 26 1001
AAAR7MAAEAAAAC2AAC Zhang San 20 1002
Six rows have been selected.
SQL> select t1.rowid,t2.rowid,t1.* from tt t1,tt t2 where t1.rowid>t2.rowid and t1.id=t2.id
2 /
ROWID ROWID NAME AGE ID
------------------ ------------------ ---------- ---------- ----
AAAR7MAAEAAAAC1AAA AAAR7MAAEAAAACzAAA Zhao 33 1003
SQL> delete from tt t where exists (select t1.rowid from tt t1,tt t2
2 where t1.rowid>t2.rowid and t1.id=t2.id and t.rowid=t1.rowid)
3 /
1 line deleted.
SQL> select rowid,tt.* from tt;
ROWID NAME AGE ID
------------------ ---------- ---------- ----
AAAR7 MAAEAAACzAAA Zhao 33 1003
AAAR7MAAEAAAAC1AAD King 44 1004
AAAR7MAAEAAAAC2AAA Zhang San 20 1000
AAAR7MAAEAAAAC2AAB Li Si 26 1001
AAAR7MAAEAAAAC2AAC Zhang San 20 1002
SQL>
When there are N duplicate records:
You can use max or min aggregate functions
SQL> select * from tt;
NAME AGE ID
---------- ---------- ----
Zhao 33 1003
Zhao 33 1003
Zhao 33 1003
King 44 1004
Zhang San 20 1000
Li Si 26 1001
Zhang San 20 1002
Seven rows have been selected.
SQL> delete from tt where rowid not in (select max(rowid) from tt t1 group by id);
Two lines have been deleted.
SQL> select * from tt;
NAME AGE ID
---------- ---------- ----
Zhao 33 1003
King 44 1004
Zhang San 20 1000
Li Si 26 1001
Zhang San 20 1002
SQL>
delete from tt t where rowid>(select min(rowid) from tt where t.id = tt.id)
About "how to use rowid to delete duplicate records" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.
