Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2010
    Location
    Sweden
    Posts
    8

    Unanswered: Urgent : Questions for db2 gurus

    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.

  2. #2
    Join Date
    Aug 2010
    Location
    Sweden
    Posts
    8
    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

  3. #3
    Join Date
    Jan 2010
    Posts
    335
    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.

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

  5. #5
    Join Date
    Jul 2009
    Posts
    150

    Arrow Try this

    Quote Originally Posted by Jenny86 View Post
    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

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

    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
    ;

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 )
    ;

  8. #8
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    what happens if i create unique constraint on these 2 columns and then set integrity?
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 08-05-10 at 11:38. Reason: Add "Note: ... "

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb deleting, using EXISTS

    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

Posting Permissions

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