9

I have a table with 3 columns as below:

one   |   two    |  three  |   name
------------------------------------
 A1       B1          C1        xyz
 A1       B1          C1        pqr      -> should be deleted
 A1       B1          C1        lmn      -> should be deleted
 A2       B2          C2        abc
 A2       B2          C2        def      -> should be deleted
 A3       B3          C3        ghi
------------------------------------

The table is not having any primary key column. I do not have any control over the table and so I can not add any primary key column.

As shown, I want to delete the rows where the combination of one, two and three column is same. So if A1B1C1 is occurring thrice (as in above e.g.), the other two should be deleted and only one should stay.

How to achieve this through just one query in DB2 ?

My requirement is for a single query as I would be running it through a java program.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Vicky
  • 16,679
  • 54
  • 139
  • 232
  • why do you prefer xyz above {pqr,lmn} and abc above def ? The first preference is first when ordered alphabetically, the second first. makes no sense to me. – wildplasser Apr 10 '12 at 11:24
  • @wildplasser: name column doesn't matter in further steps. So there is no such preference... any two could be deleted.. – Vicky Apr 10 '12 at 11:27

7 Answers7

24

(This assumes you're on DB2 for Linux/Unix/Windows, other platforms may vary slightly)

DELETE FROM
    (SELECT ROWNUMBER() OVER (PARTITION BY ONE, TWO, THREE) AS RN
     FROM SESSION.TEST) AS A
WHERE RN > 1;

Should get you what you're looking for.

The query uses the OLAP function ROWNUMBER() to assign a number for each row within each ONE, TWO, THREE combination. DB2 is then able to match the rows referenced by the fullselect (A) as the rows that the DELETE statement should remove from the table. In order to be able to use a fullselect as the target for a delete clause, it has to match the rules for a deletable view (see "deletable view" under the notes section).

Below is some proof (tested on LUW 9.7):

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST (
    one CHAR(2),
    two CHAR(2),
    three CHAR(2),
    name CHAR(3)
) ON COMMIT PRESERVE ROWS;

INSERT INTO SESSION.TEST VALUES 
    ('A1', 'B1', 'C1', 'xyz'),
    ('A1', 'B1', 'C1', 'pqr'),
    ('A1', 'B1', 'C1', 'lmn'),
    ('A2', 'B2', 'C2', 'abc'),
    ('A2', 'B2', 'C2', 'def'),
    ('A3', 'B3', 'C3', 'ghi');

DELETE FROM
    (SELECT ROWNUMBER() OVER (PARTITION BY ONE, TWO, THREE) AS RN
     FROM SESSION.TEST) AS A
WHERE RN > 1;

SELECT * FROM SESSION.TEST;

Edit 2 March 2017:

In response to the question from Ahmed Anwar, if you need to capture what was deleted, you can also combine the delete with a "data change statement". In this example you could do something like the following, which would give you the "rn" column, one, two, and three:

SELECT * FROM OLD TABLE (
    DELETE FROM
        (SELECT 
             ROWNUMBER() OVER (PARTITION BY ONE, TWO, THREE) AS RN
            ,ONE
            ,TWO
            ,THREE
         FROM SESSION.TEST) AS A
    WHERE RN > 1
) OLD;
bhamby
  • 15,112
  • 1
  • 45
  • 66
  • Its working.. however, could you please elaborate your answer with a little explanation as to what exactly is happening... – Vicky Apr 11 '12 at 05:21
  • @NikunjChauhan I updated my answer slightly to include some clarification. – bhamby Apr 11 '12 at 13:12
  • Is there a way to use this query to SELECT the duplicates without the original record? I've tried but I'm not too familiar with the syntax so any changes I make result in errors – Ahmed Anwar Mar 01 '17 at 12:28
  • Or if could somehow use the '(SELECT ROWNUMBER() OVER (PARTITION BY ONE, TWO, THREE) AS RN FROM SESSION.TEST) AS A' query to return ONE as well so I can use it in an outer query. I'm asking because there are foreign keys, so I have to delete from other tables before I can actually delete the duplicate records – Ahmed Anwar Mar 01 '17 at 12:30
  • 1
    @AhmedAnwar I've edited the answer to give an example of what I think you want. – bhamby Mar 03 '17 at 04:33
  • @bhamby that's great! Thanks a lot! – Ahmed Anwar Mar 07 '17 at 17:23
  • I actually find myself doing more and more queries with the row_number() function. Mosty to extract n of a type records for testing. Same principle – danny117 Dec 08 '17 at 19:18
2
DELETE FROM the_table tt
WHERE EXISTS ( SELECT *
    FROM the_table ex
    WHERE ex.one = tt.one
    AND ex.two = tt.two
    AND ex.three = tt.three
    AND ex.zname < tt.zname -- tie-breaker...
    );

Notes: your SQL-dialect may vary. Note2: "name" is a reserved word on some platforms. Better avoid it.

wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • But in your answer we are assuming that zname would be unique for each row. Since table does not have a primary key constraint, this assumption would be invalid when same name would be present in duplicate records. – Vicky Apr 10 '12 at 11:39
  • That is correct. In that case you'll have to use a row_number. Most DMBMs have a row_number, but the name varies between platforms. (row_num, row_id,tid, ...) Check the documentation. – wildplasser Apr 10 '12 at 12:08
1

a variation of @a_horse_with_no_name answer db2 for iseries without using group by clause and in clause. It actually works 

DELETE from the_table a 
where rrn(a) < (
select max(rrn(a)) from the_table b 
where a.one = b.one and a.two = b.two and a.three = b.three
)
danny117
  • 5,581
  • 1
  • 26
  • 35
0
Please take backup of table before deleting the data

Delete from table where Name in (select name from table
group by one,two,three
having count(*) > 2)

You can use

     DELETE from TABLE Group by one,two,three Having count(*) > 2;
Sunil Chavan
  • 524
  • 5
  • 15
0
DELETE  FROM Table_Name
WHERE   Table_Name_ID NOT IN ( SELECT  MAX(Table_Name_ID)
                                    FROM    Table_Name
                                    GROUP BY one ,
                                             two, 
                                             three )

one two threee are your repeated columns and Table_Name_ID is PK

levi
  • 3,451
  • 6
  • 50
  • 86
0

This is a variation of levenlevi's answer that does not require a primary key on the table (Can't test the syntax right now thow)

DELETE FROM the_table
WHERE  rid_bit(the_table) NOT IN (SELECT MAX(rid_bit(the_table))
                                  FROM the_table
                                  GROUP BY one,two,three)

I think on iSeries the rid_bit() is not supported, but rrn() save the same purpose

0

For other using a very old version of db2 SQL: A combination of these posts helped identify and remove the dups from 2 batches posted twice.

SELECT   * FROM     LIBRARY.TABLE a
WHERE    a.batch in (115131, 115287)
AND      EXISTS ( SELECT 1 from LIBRARY.TABLE d 
    WHERE d.batch in (115131, 115287)
     AND a.one = d.one AND a.two = d.two AND a.three = d.three 
    GROUP BY d.one, d.two, d.three 
    HAVING count(*) <> 1 )

    AND RRN(a) > (SELECT MIN(RRN(b)) FROM LIBRARY.TABLE b 
        WHERE b.batch in (115131, 115287)
        AND a.one = b.one AND a.two = b.two AND a.three = b.three );
Tom S.
  • 1
  • 1