Use RowID to find and delete duplicate rows

zhaozj2021-02-16  81

SQL> CREATE TABLE LUNAR (

2 C1 char (4),

3 c2 varchar2 (20)

4)

5 /

The table has been created.

SQL> INSERT INTO LUNAR VALUES ('1111', '1111');

It has created a row.

SQL> INSERT INTO LUNAR VALUES ('1112', '1111');

It has created a row.

SQL> INSERT INTO LUNAR VALUES ('1113', '1111');

It has created a row.

SQL> INSERT INTO LUNAR VALUES ('1114', '1111');

It has created a row.

SQL> commit;

Submitted.

SQL>

Create Record:

SQL> INSERT INTO LUNAR SELECT * from Lunar;

4 lines have been created.

SQL> commit;

Submitted.

SQL> SELECT ROWID, C1, C2 from Lunar;

RowID C1 C2

----------------------------------------

Aaag / Oaagaaaaamaaaaa 1111 1111

Aaag / Oaagaaaaamaab 1112 1111

Aaag / Oaagaaaaamaac 1113 1111

Aaag / Oaagaaaaamaad 1114 1111

Aaag / Oaagaaaaaaaaaaaaaaaaaaaaaae 1111 1111

Aaag / Oaagaaaaaaaaf 1112 1111

Aaag / Oaagaaaaamaaag 1113 1111

Aaag / Oaagaaaaaaaa 1114 1111

8 lines have been selected.

SQL>

Isolated repeated record

SQL> SELECT ROWID, C1, C2

2 from lunar

3 where lunar.rowid! = (SELECT MAX (ROWID)

4 from Lunar B

5 WHERE LUNAR.C1 = B.c1 and lunar.c2 = b.c2);

RowID C1 C2

----------------------------------------

Aaag / Oaagaaaaamaaaaa 1111 1111

Aaag / Oaagaaaaamaab 1112 1111

Aaag / Oaagaaaaamaac 1113 1111

Aaag / Oaagaaaaamaad 1114 1111

SQL>

Delete Record:

SQL> Delete from Lunar A

2 WHERE A.ROWID! = (Select Max (RowID)

3 from Lunar B

4 WHERE A.C1 = B.c1 and a.c2 = b.c2);

4 lines have been deleted.

SQL> commit;

Submitted.

SQL> SELECT ROWID, C1, C2 from Lunar;

RowID C1 C2

----------------------------------------

Aaag / Oaagaaaaaaaaaaaaaaaaaaaaaae 1111 1111

Aaag / Oaagaaaaaaaaf 1112 1111

Aaag / Oaagaaaaamaaag 1113 1111

Aaag / Oaagaaaaaaaa 1114 1111

SQL>

转载请注明原文地址:https://www.9cbs.com/read-13925.html

New Post(0)