Urgent : Questions for db2 gurus

Jenny86 · #1 (**permalink**) 08-05-10, 06:22

I have a table defined as
$ db2 "create table demo (col1 int, col2 int, col3 int, col4 int)"

I filled it with data as:
$ db2 "select * from demo"
COL1 COL2 COL3 COL4
----------- ----------- ----------- -----------
1 2 3 4
1 8 5 6
1 2 999 89
4 2 5 6

4 record(s) selected.

Jenny86 · #2 (**permalink**) 08-05-10, 06:23

I want to delete the rows which has same COL1 and COL2.

result should be like....

COL1 COL2 COL3 COL4
----------- ----------- ----------- -----------
1 2 3 4
1 8 5 6
4 2 5 6

nvk@vhv · #3 (**permalink**) 08-05-10, 07:51

Is there any rule which duplicate should be deleted?

Wat happens here?
COL1 COL2 COL3 COL4
----------- ----------- ----------- -----------
1 2 3 4
1 2 4 3
1 2 3 4
1 2 2 3
1 2 1 0

The data in the tables is not ordered, so you may not have the same result after deletes.

You can also create an index to ensure uniqueness for col1, col2.

umayer · #4 (**permalink**) 08-05-10, 08:12

this can't be done with a simple SQL-statement as you can't DELETE FROM a table, which is referenced in the WHERE-clause.
So a
DELETE FROM demo WHERE ( col1,col2 ) IN ( SELECT col1,col2 FROM demo GROUP BY col1,col2 HAVING COUNT(*) > 1 ) doesn't work.

You can

- UNLOAD the data, then
- DELETE all rows from the table, then
- CREATE a unique index on col1,col2, then
- RELOAD the data into the table.

The load-utiliy will discard all rows violating the unique index

or you can

- CREATE an auxiliary table with col1 and col2, then
- INSERT INTO auxiliary.table (col1,col2 ) SELECT col1,col2 FROM demo GROUP BY col1,col2 HAVING COUNT(*) > 1 , then
- DELETE FROM demo WHERE ( col1,col2 ) IN ( SELECT col1,col2 FROM auxiliary.table ) a , then
- DROP the auxiliary.table

DB2Plus · #5 (**permalink**) 08-05-10, 08:20

Quote:

Originally Posted by Jenny86

I have a table defined as
$ db2 "create table demo (col1 int, col2 int, col3 int, col4 int)"

I filled it with data as:
$ db2 "select * from demo"
COL1 COL2 COL3 COL4
----------- ----------- ----------- -----------
1 2 3 4
1 8 5 6
1 2 999 89
4 2 5 6

4 record(s) selected.

This query will works:

Code:

delete from demo d1
where 
exists (select 1 from demo d2
where (d2.col3 > d1.col3 or d2.col4 > d1.col4)
and d2.col1 = d1.col1
and d2.col2 = d1.col2 )

Kara

tonkuma · #6 (**permalink**) 08-05-10, 08:30

Quote:

Is there any rule which duplicate should be deleted?

Wat happens here?
COL1 COL2 COL3 COL4
----------- ----------- ----------- -----------
1 2 3 4
1 2 4 3
1 2 3 4
1 2 2 3
1 2 1 0

The data in the tables is not ordered, so you may not have the same result after deletes.

It must be true, if there is no rule which duplicate should be deleted.

Quote:

this can't be done with a simple SQL-statement as you can't DELETE FROM a table, which is referenced in the WHERE-clause.

Even if there are some duplicate rows(all columns are same), you can delete rows with duplicate keys(i.e. col1 and col2) by a DELETE statement, like this:
(Keep a lowest row ordered by col3 , col4 in rows with same col1 and col2.)

Code:

DELETE FROM
       (SELECT r.*
             , ROW_NUMBER()
                 OVER(PARTITION BY col1 , col2
                          ORDER BY col3 , col4) rn
          FROM demo r
       ) r
 WHERE rn > 1
;

tonkuma · #7 (**permalink**) 08-05-10, 08:51

Code:

delete from demo d1
where 
exists (select 1 from demo d2
where (d2.col3 > d1.col3 or d2.col4 > d1.col4)
and d2.col1 = d1.col1
and d2.col2 = d1.col2 )

If there are a pair of rows with d2.col3 > d1.col3 AND d2.col4 < d1.col4, both will be deleted.

Here is an example.

Before delete:

Code:

------------------------------ Commands Entered ------------------------------
SELECT * FROM demo;
------------------------------------------------------------------------------

COL1        COL2        COL3        COL4       
----------- ----------- ----------- -----------
          1           2           3           4
          1           8           5           6
          1           2           4           3
          4           2           5           6

  4 record(s) selected.

DELETE statement:

Code:

------------------------------ Commands Entered ------------------------------
delete from demo d1
where 
exists (select 1 from demo d2
where (d2.col3 > d1.col3 or d2.col4 > d1.col4)
and d2.col1 = d1.col1
and d2.col2 = d1.col2 );
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

After delete:

Code:

------------------------------ Commands Entered ------------------------------
SELECT * FROM demo;
------------------------------------------------------------------------------

COL1        COL2        COL3        COL4       
----------- ----------- ----------- -----------
          1           8           5           6
          4           2           5           6

  2 record(s) selected.

The following example may fix it.
But, if there are a pair of rows which are all columns identical, both rows may not be deleted.

Code:

delete from demo d1
where 
exists (select 1 from demo d2
where (d2.col3 > d1.col3 OR d2.col3 = d1.col3 AND d2.col4 > d1.col4)
and d2.col1 = d1.col1
and d2.col2 = d1.col2 )
;

MarkhamDBA · #8 (**permalink**) 08-05-10, 10:03

what happens if i create unique constraint on these 2 columns and then set integrity?

tonkuma · #9 (**permalink**) 08-05-10, 10:33

Quote:

what happens if i create unique constraint on these 2 columns and then set integrity?

I agree that creating a unique constraint(or creating a unique index, like umayer wrote) is a better solution than deleting duplicates after load/insert.

Note: adding a unique constraint to existing table and set integrity will not automatically remove duplicates.

Lenny77 · #10 (**permalink**) 08-05-10, 11:18

This query is not solving the problem of duplicates, when all columns are equal, but could solve the problem found out by tonkuma:

Code:

delete from demo d1
where 
exists (select 1 from demo d2
where 
(d2.col3 > d1.col3 
or 
((d2.col3 = d1.col3) and (d2.col4 > d1.col4))
and d2.col1 = d1.col1
and d2.col2 = d1.col2 )

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech