Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Delete ALL! duplicate rows from table.

    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
    Code:
    COL1
    -----------
              1
              1
              1
              2
              2
              3
    After deleting the result should be without duplicates, so:
    Code:
    COL1
    -----------
              1
              2
              3
    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
    Code:
    COL1        MYCOUNT
    ----------- --------------------
              1                    1
              1                    2
              1                    3
              2                    1
              2                    2
              3                    1
    But how to write a delete statement? Any idea?

    My system: db2 v8.2 fp 9 on Windows XP SP2
    Thanks,
    Grofaty
    Last edited by grofaty; 06-13-07 at 08:21.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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;

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126

    use a stored procedure

    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by Peter.Vanroose
    - 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

  5. #5
    Join Date
    Dec 2005
    Posts
    273
    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 ?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    or SELECT DISTINCT, DROP, RENAME as outlined in post #2?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by umayer
    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
    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.)
    Last edited by Peter.Vanroose; 06-14-07 at 12:35.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Jun 2007
    Posts
    197
    there no concept of ROWID as in oracle you cannot differentiate similar
    rows

    is there any way to delete from using sql

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    post #2 is sql...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ankur02018
    is there any way to delete from using sql
    Yes: you can use REORG with a DISCARD WHEN condition. (At least, on z/OS; don't know about LUW.)
    Last edited by Peter.Vanroose; 06-14-07 at 13:32.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by umayer
    Is it possible to EXPORT the table with SELECT DISTINCT ...
    and then reIMPORT the data into the table ?
    Yes:
    Code:
    db2 export to temp.ixf of ixf select distinct * from table1
    db2 import from temp.ixf of ixf replace into table1
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    Join Date
    Jun 2007
    Posts
    197
    Quote Originally Posted by Peter.Vanroose
    Yes: you can use REORG with a DISCARD WHEN condition. (At least, on z/OS; don't know about LUW.)
    can you please give full syntax

  13. #13
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ankur02018
    can you please give full syntax
    Example: remove all rows from mytable for which col1 > 7:
    Code:
    REORG TABLESPACE dbname.tsname DISCARD FROM TABLE mytable WHEN (col1 > 7)
    (See page 487 of the v8 Utility Guide for an other example.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  14. #14
    Join Date
    Jun 2007
    Posts
    197
    Quote Originally Posted by r937
    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;


    gr8 work done

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

    rename table table2 to table1:

  15. #15
    Join Date
    Jun 2007
    Posts
    197
    Quote Originally Posted by Peter.Vanroose
    Example: remove all rows from mytable for which col1 > 7:
    Code:
    REORG TABLESPACE dbname.tsname DISCARD FROM TABLE mytable WHEN (col1 > 7)
    (See page 487 of the v8 Utility Guide for an other example.)
    its not working please check

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •