Deleting duplicate rows from a table | Db2

In this article we will see and learn about how to delete duplicate rows from a table in Db2.

1) Run SELECT command to find duplicate rows:

Syntax: db2 "select * from rank order by <column>"

db2 "select * from rank order by dupsample"

DUPSAMPLE         NAME                         
----------- ------------------------------
          1 sample1                       
          2 sample2                       
          3 sample3                       
          3 sample3                       
          4 sample4                       

  5 record(s) selected.

2) Now execute the below command by putting row number in your data output:

Syntax: db2 "SELECT ROWNUMBER() OVER (ORDER BY <column>) \ AS RANK,<column> FROM <table name>"

db2 "SELECT ROWNUMBER() OVER (ORDER BY DUPSAMPLE) AS RANK,DUPSAMPLE FROM rank"

Output: 
RANK                 DUPSAMPLE       
-------------------- -----------
                   1           1
                   2           2
                   3           3
                   4           3
                   5           4

  5 record(s) selected.

3) Then run the follow SELECT to delete duplicate rows:

Syntax: db2 "delete from (select ROWNUMBER() OVER (ORDER BY <column>) FROM \ <table name>) as E(pos) where pos=<row number to be deleted>"

db2 "delete from (select ROWNUMBER() OVER (ORDER BY DUPSAMPLE) FROM rank) as E(pos) where pos=3"

Output:
DB20000I  The SQL command completed successfully.

db2 "select * from rank order by dupsample"

Output:
DUPSAMPLE         NAME                         
----------- ------------------------------
          1 sample1                       
          2 sample2                       
          3 sample3                       
          4 sample4                       

  4 record(s) selected.

That's all in this post. If you liked this blog and interested in knowing more about IBM Db2. Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com