Hi,
I would like to delete all duplicate rows from table.

Sample:
CREATE TABLE DB2ADMIN.TABLE1 (COL1 INT)
INSERT INTO DB2ADMIN.TABLE1 VALUES (1),(1),(1),(2),(2),(3)
SELECT * FROM DB2ADMIN.TABLE1
After deleting the result should be without duplicates, so:
I know I can write an SELECT that "marks" all rows that are duplicate and should be deleted - mycount column is greater than 1. (Marked bold in following table)

SELECT COL1, ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL1) AS MYCOUNT FROM DB2ADMIN.TABLE1
But how to write a delete statement? Any idea?

My system: db2 v8.2 fp 9 on Windows XP SP2
Thanks,
Grofaty

create table db2admin.table2 (col1 int);

insert into db2admin.table2
select distinct col1 from db2admin.table1;

drop table db2admin.table1;

rename table db2admin.table2 to db2admin.table1;

It's clear that some sequential logic is needed in this case, since because two identical rows cannot be distinguished, either both or none of them will be deleted.
row_number() is essentially "sequential logic", but I don't immediately see a way to use it in a delete statement.

An other option (using sequential logic) is to write a stored procedure:
- Open a cursor: DECLARE c CURSOR FOR SELECT col1 FROM table1 FOR UPDATE
- set prevv1 to 0
- iteratively FETCH c INTO :v1
- in the iteration, compare v1 with prevv1, and DELETE WHERE CURRENT OF c only when v1 = prevv1
- in the iteration, copy v1 to prevv1.

I don't believe this will work. The order of the rows is random so it is mere chance that two identical rows are fetched in succession

Is it possible to EXPORT the table with SELECT DISTINCT ...
and then reIMPORT the data into the table ?

or EXPORT , DELETE all rows, CREATE an unique index and then IMPORT the data using the INSERT_UPDATE option ?

or SELECT DISTINCT, DROP, RENAME as outlined in post #2?

Of course, you're right. Sorry.
Just add "ORDER BY col1" to the cursor declaration.
(Note that in this case an index MUST be defined in col1, since only and index-based access will allow the FOR UPDATE in combination with ORDER BY.)

there no concept of ROWID as in oracle you cannot differentiate similar
rows

is there any way to delete from using sql

post #2 is sql...

Yes: you can use REORG with a DISCARD WHEN condition. (At least, on z/OS; don't know about LUW.)

Yes:

can you please give full syntax

Example: remove all rows from mytable for which col1 > 7:(See page 487 of the v8 Utility Guide for an other example.)

gr8 work done

but only last statement was not working
it work as below:--

rename table table2 to table1:

its not working please check