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.