Duplicate data query and delete operations in Oracle
--1. Duplicate records of Username= ' Lingjie ' in the query table
Select Userid,username from NMB where username in (
Select username from NMB GROUP by username have count (username) >1)
--2. Delete Username duplicate data in a table, preserving only the smallest rowid
Delete from NMB where username in
(select username from NMB GROUP by username have count (username) >1)
and rowID not in (select min (rowid) from NMB GROUP by username have count (username) >1)
--3. Querying extra duplicate records in a table (multiple fields)
SELECT * FROM NMB N
where (N.USERNAME,N.PWD) in
(select Username,pwd from NMB GROUP by USERNAME,PWD have Count (*) >1)
--4. Delete duplicate records (multiple fields) in a table, preserving only the smallest rowid
Delete from NMB n
where (N.USERNAME,N.PWD) in
(select Username,pwd from NMB GROUP by USERNAME,PWD have Count (*) >1)
and rowID not in
(select min (rowid) from NMB GROUP by USERNAME,PWD have Count (*) >1)
--5. Finding redundant duplicate records (multiple fields) in a table, without ROWID minimum records
SELECT * FROM NMB N
where (N.USERNAME,N.PWD) in
(select Username,pwd from NMB GROUP by USERNAME,PWD have Count (*) >1)
and rowID not in
(select min (rowid) from NMB GROUP by USERNAME,PWD have Count (*) >1)