Five situations in which DB2 deletes duplicate records

What is the difference between how DB2 deletes repeated records and other database systems? The following describes how to delete Repeated Records in five DB2 versions.

1. Search for redundant duplicate records in the Table. duplicate records are determined based on the peopleId of a single field.

Select * from people where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)

2. DB2 deletes duplicate records. duplicate records are determined based on the peopleId of a single field. Only the records with the smallest rowid are retained.
Delete from people where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1) and rowid not in (select min (rowid) from people group by peopleId having count (peopleId)> 1)

3. Search for redundant fields in the table)

Select * from vitae a where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)

4. DB2 deletes multiple fields with the least rowid.

Delete from vitae a where (. peopleId,. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) and rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)

5. Search for multiple fields of redundant record records in the table), excluding records with the smallest rowid

Select * from vitae a where (. peopleId,. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) and rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)

How to Create a DB2 database in an aix Environment

Nine common DB2 commands

How to Create a tablespace in DB2 under aix

How to Create a tablespace in DB2 in windows

How to delete tables with large data volumes in DB2