Implementation of deleting duplicate data in DB2

The operations that we often use to delete duplicate data in DB2. The following describes how to delete duplicate data in DB2, hoping to help you learn how to delete duplicate data in DB2.

Use ROW_NUMBER to delete duplicate data
If the table TAB contains columns a, B, and c, you can use the following statements to delete duplicate rows that are the same for a, B, and c.

 
 

  
  
delete from (select * from (select a,b,c,row_number() over(partition by a,b,c order by a,b,c) as row_num from tab) as e where row_num >1) 
 
 

If the data volume is too large, use the following method:

 
 

  
  
Create table emp_profile_temp like emp_profile; 
 
 

Large data volumes use LOAD FROM CURSUR

 
 

  
  
DECLARE mycursor CURSOR FOR SELECT distinct * FROM emp_profile;  
  
  
LOAD FROM mycursor OF CURSOR INSERT INTO emp_profile_temp; 
 
 

 
 

  
  
drop table emp_profile;  
  
  
rename table emp_profile_temp to emp_profile  
 
 

DB2 common table expression usage

Cross-database access to DB2 tables

DB2 tablespace size limit

Lotus Notes calls the DB2 Stored Procedure

DB2 connection Server Configuration