DB2 database Duplicate record Delete SQL statement

--1. Querying for duplicate data

The code is as follows	Copy Code
SELECT * from TableA where catentry_id to (select distinct catentry_id from TableA GROUP by CATENTRY_ID,DESCITEM_ID has COUNT (*) >1) @

--2. Create a temporary table that stores all duplicate data in the table

The code is as follows	Copy Code
CREATE TABLE DETAILTEMP1 like tablea@ INSERT INTO DETAILTEMP1 SELECT * FROM TableA where catentry_id in (select distinct catentry_id from TableA GROUP by CATENTRY_ID,DESCITEM_ID has count (*) >1) @ SELECT * FROM detailtemp1@

--3. Delete all occurrences of a record from the original table

The code is as follows	Copy Code
Delete from TableA where catentry_id into (select distinct catentry_id from TableA GROUP by catentry_id,descitem_id having C Ount (*) >1) @

--4. Insert a duplicate record from a temporary table into the original table with GROUP by group

The code is as follows	Copy Code
INSERT INTO TableA Select Catentry_id,descitem_id,max (content) From DETAILTEMP1 GROUP BY catentry_id,descitem_id@ SELECT * from TableA where catentry_id to (select distinct catentry_id from TableA GROUP by CATENTRY_ID,DESCITEM_ID has COUNT (*) >1) @

--5. Delete temporary table

The code is as follows	Copy Code
drop table detailtemp1@ --end

There are several other ways to share

2, DB2 delete duplicate records, duplicate records are based on a single field (Peopleid) to judge, leaving only rowid the smallest record

The code is as follows	Copy Code
Delete from people where Peopleid into (select Peopleid from People GROUP by Peopleid have count (Peopleid) > 1) and row ID not in (select min (rowid) from people GROUP by Peopleid has count (Peopleid) >1)

3. Find redundant records in the table (multiple fields)

The code is as follows	Copy Code
SELECT * from Vitae a WHERE (A.PEOPLEID,A.SEQ) in (select Peopleid,seq to Vitae GROUP by PEOPLEID,SEQ have Count (*) &G T 1)

4, DB2 delete duplicate records (multiple fields), leaving only the ROWID minimal records

The code is as follows	Copy Code
Delete from Vitae a where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq to Vitae GROUP by PEOPLEID,SEQ have count (*) > 1) and rowID (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)

5, look for redundant records in the table (multiple fields), does not contain the smallest ROWID records

The code is as follows	Copy Code
SELECT * from Vitae a WHERE (A.PEOPLEID,A.SEQ) in (select Peopleid,seq to Vitae GROUP by PEOPLEID,SEQ have Count (*) &G T 1) and rowID (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)

6. Handle the bas_information in the heavy, according to

code is as follows

copy code

t.pisfield001 , T.pisfield011,t.areacode Export to E:/bas_information.txt of Del Select S.households, s.pisfield000, s.pisfield001 , s.pisfield002, s.pisfield003, s.pisfield004, s.pisfield005, s.pisfield006, s.pisfield011, s.PISFIELD012, S. PISFIELD013, s.pisfield014, s.pisfield015, s.pisfield016, S.deaddate, S.qianrudate, S.zhiye, s.jiating, S.AREACODE , S.reg_date, S.islogout from (select T.*,rownumber () over (partition by T.pisfield001,t.pisfield011,t.areacode) as RN from Bas_information t) s where s.rn = 1;