SQL Delete duplicate records

Test data preparation

Give Test_user access to the Scott User EMP table

Grant Select  on  to Test_user;

Use Test_user to create an EMP table based on the Scott User's EMP table

Create Table  as Select *  from Scott.emp;

Insert the same data again

Insert  into Select *  from EMP;

Querying Current data

Select *  from Order  by ASC;

You can see that each record appears twice.

Delete duplicate records

1, delete redundant records in the table, duplicate records are based on a single field (EMPNO) to judge, leaving only the smallest ROWID records

Delete  fromEMPwhereEmpnoinch(SelectEmpno fromEmpGroup  byEmpno having Count(empno)> 1) androwID not inch(Select min(ROWID) fromEmpGroup  byEmpno having Count(empno)>1)

2, delete redundant records in the table, duplicate records are based on a number of fields (Empno, ename) to judge, leaving only the smallest records ROWID

Delete  fromEMP awhere(A.empno, A.ename)inch(SelectEmpno,ename fromEmpGroup  byEmpno,ename having Count(*)> 1) androwID not inch(Select min(ROWID) fromEmpGroup  byEmpno,ename having Count(*)>1)

SQL Delete duplicate records