Implementation of deleting duplicate data in DB2

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.

The following is a code snippet:

 
 

  
  
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:

The following is a code snippet:

 
 

  
  
Create table emp_profile_temp like emp_profile; 
 
 

Large data volumes use LOAD FROM CURSUR

The following is a code snippet:

 
 

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

The following is a code snippet:

 
 

  
  
drop table emp_profile; rename table emp_profile_temp to emp_profile