Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    7

    Unanswered: Any SQL statements for checking duplicate data in a table

    Hi,

    I wonder if there any syntax or SQL statements that I could use to check for duplicate data in a table (e.g. temp table) ? Thks all in advance !!!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    A really simple way would be to have DB2 count them.
    SELECT cola,colb,colc,...,count(*) as qty from mytable group by cola,colb,colc,...

    Those rows in the resultset with a QTY > 1 have duplicate rows.

    HTH

    Andy

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ARWinner
    Those rows in the resultset with a QTY > 1 have duplicate rows.
    andy, your sql is fine, but your closing statement is not

    those rows in the resultset with qty > 1 have duplicate selected columns in the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If all the columns are in the group by clause, then it would count the duplicate rows. That is why I used the ellipse (...) to signify that all columns should be specified.

    Andy

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    To be more specific:

    SELECT cola, colb, colc, count(*)
    from mytable
    group by cola, colb, colc
    having count(*) > 1
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    May 2004
    Posts
    7
    thks guys for all the help

Posting Permissions

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