Shulou(Shulou.com)06/01 Report--

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.

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.