Personal tools
You are here: Home DB2 How To's How to delete duplicate rows from a table.
Navigation
Log in


Forgot your password?
 
Document Actions

How to delete duplicate rows from a table.

Show a select to delete duplicate rows.

 

 

If you have duplicate rows as the follow example:

 

 

db2 "select * from rank order by cod"

COD         NAME                         
----------- ------------------------------
          1 teste1                       
          2 teste2                       
          3 teste3                       
          3 teste3                       
          4 teste4                       

  5 record(s) selected.

 

You can put row number in your data output, perform the command below:

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

Output:

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

RANK                 COD       
-------------------- -----------
                   1           1
                   2           2
                   3           3
                   4           3
                   5           4

  5 record(s) selected.

Then, You can delete the duplicate rows running the follow select:

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

Output:

db2 "delete from (select ROWNUMBER() OVER (ORDER BY COD) FROM rank) as E(pos) where pos=3"
DB20000I  The SQL command completed successfully.

db2 "select * from rank order by cod"

COD         NAME                         
----------- ------------------------------
          1 teste1                       
          2 teste2                       
          3 teste3                       
          4 teste4                       

  4 record(s) selected.

Related content
Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls