Oracle deletes duplicate records

 

Author: skate

Time:

In an Oracle database, rowid is unique and identifies the file, block, and row to be recorded.
On Repeated Records, the contents of all columns may be the same, but their rowid
It is different, so I only need to delete the largest rowid of the Repeated Records.
Let's get started with a bunch of things.

1. Delete duplicate rows

SQL> select * from BB;

ID name
--------------------
3 SQ
4 SQA
5 SQA
6 SQA
7 SQA
2
1 s

You have selected 7 rows.

Used time: 00: 00: 00.00
SQL>

2. Insert duplicate records

SQL> insert into BB select * from BB;

Seven rows have been created.

Used time: 00: 00: 00.00
SQL>

3. Search for duplicate records

SQL> select * from BB where rowid! = (
2 select max (rowid) from BB t
3 where BB. ID = T. ID
4 and BB. Name = T. Name );

ID name
--------------------
3 SQ
4 SQA
5 SQA
6 SQA
7 SQA
2
1 s

You have selected 7 rows.

Used time: 00: 00: 00.03
SQL>

4. Delete duplicate records

SQL> Delete form BB where rowid! =
2 (select max (rowid) from BB t
3 where BB. ID = T. ID
4 and BB. Name = T. Name );
Delete form BB where rowid! =
*
Row 3 has an error:
ORA-00942: Table or view does not exist

Used time: 00: 00: 00.06
SQL> L
1 Delete form BB where rowid! =
2 (select max (rowid) from BB t
3 where BB. ID = T. ID
4 * and BB. Name = T. Name)
SQL> 1
1 * Delete form BB where rowid! =
SQL> C/form/from
1 * Delete from BB where rowid! =
SQL> L
1 Delete from BB where rowid! =
2 (select max (rowid) from BB t
3 where BB. ID = T. ID
4 * and BB. Name = T. Name)
SQL>/

Seven rows have been deleted.

Used time: 00: 00: 00.01
SQL>
 

 

 

It's easy to finish the work here.

 

 

 

Netizen: DD, I pointed out that there is a problem with my example. I have a problem here. My experiment is in SQL> insert into BB select * from BB; and there is no commit afterwards, the following is my test. You can see:

SQL> select * from AA;

ID name
----------------------------------------
1 22
2 23
3 24
4 25

SQL> set term on
SQL> select * from AA;

ID name
----------------------------------------
1 22
2 23
3 24
4 25

SQL> set TIMI on
SQL> select * from AA;

ID name
----------------------------------------
1 22
2 23
3 24
4 25

Used time: 00: 00: 00.00
SQL> set feedback on
SQL> select * from AA;

ID name
----------------------------------------
1 22
2 23
3 24
4 25

Four rows have been selected.

Used time: 00: 00: 00.00
SQL> insert into AA select * from AA;

Four rows have been created.

Used time: 00: 00: 00.01
SQL> select * from AA;

ID name
----------------------------------------
1 22
2 23
3 24
4 25
1 22
2 23
3 24
4 25

Eight rows have been selected.

Used time: 00: 00: 00.01
SQL> commit;

Submitted.

Used time: 00: 00: 00.00
SQL> select * from AA where rowid! = (Select max (rowid) from AA t where BB. ID = T. I
D and BB. Name = T. Name );
Select * from AA where rowid! = (Select max (rowid) from AA t where BB. ID = T. ID and
BB. Name = T. Name)

*
Row 3 has an error:
ORA-00904: "BB". "name": Invalid identifier

Used time: 00: 00: 00.03
SQL> select * from AA where rowid! = (Select max (rowid) from AA t where AA. ID = T. I
D and AA. Name = T. Name );

ID name
----------------------------------------
1 22
2 23
3 24
4 25

Four rows have been selected.

Used time: 00: 00: 00.04
SQL> Delete from AA where rowid! = (Select max (rowid) from AA t where AA. ID = T. I
D and AA. Name = T. Name );

Four rows have been deleted.

Used time: 00: 00: 00.01
SQL> commit;

Submitted.

Used time: 00: 00: 00.00
SQL> select * from AA;

ID name
----------------------------------------
1 22
2 23
3 24
4 25

Four rows have been selected.

Used time: 00: 00: 00.01
SQL>

This result is correct !!!

 

Thank you, DD.