Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002
    Posts
    456

    Unanswered: Removing duplicate rows

    DB2 UDB V7.2 AIX

    Hi:

    I have a table which is copy of another table but does not contain the same structure for primary key; in original table PK consists of five columns but in this table it's made up for four columns. Thus I end up with duplicates. I have created the table and populated with data but can't create PK untill I clean up the duplicate rows.

    So, is there any easy way to delete these rows i.e. using some kick-ass SQL? I have over 2000 duplicate rows so doing them manually is pain at this time.

    Thanks in advance.

    dollar

  2. #2
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Distinct keyword

    Hi Dollar

    Have you tried:

    Select distinct * from mytable

    BOW
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  3. #3
    Join Date
    Aug 2002
    Location
    delhi, india
    Posts
    4
    HTH:

    db2 => select rownumber() over (partition by deptno) from department

    1
    --------------------
    1
    1
    1
    1
    1
    1
    1
    1
    1
    2

    10 record(s) selected.

    db2 => create view tempv(rno) as select rownumber() over (partition by
    deptno) f
    rom department
    DB20000I The SQL command completed successfully.

    db2 => delete from tempv where rno > 1
    DB20000I The SQL command completed successfully.
    db2 => select * from department

    DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION
    ------ ----------------------------- ------ -------- ----------------
    A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 -
    B01 PLANNING 000020 A00 -
    C01 INFORMATION CENTER 000030 A00 -
    D01 DEVELOPMENT CENTER - A00 -
    D11 MANUFACTURING SYSTEMS 000060 D01 -
    D21 ADMINISTRATION SYSTEMS 000070 D01 -
    E01 SUPPORT SERVICES 000050 A00 -
    E11 OPERATIONS 000090 E01 -
    E21 SOFTWARE SUPPORT 000100 E01 -

    9 record(s) selected.

    db2 =>
    db2 => select rownumber() over (partition by deptno order by mgrno) from
    department

    1
    --------------------
    1
    --------------------
    1
    1
    1
    1
    1
    1
    1
    1
    1

    9 record(s) selected.

  4. #4
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    distinct rules!

    If there is no other columns in the new table than the
    key columns, select distinct * followed by an insert, is by
    far the easiest approach.
    If there are columns to be preserved,
    apart from the PK, you have to make an informed decision, on
    which rows to delete - supposedly according to some rule.
    If the remaining information is of no consequence, again the
    select distinct * is the easiest approach.

    BOW
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  5. #5
    Join Date
    Sep 2002
    Posts
    456

    Re: distinct rules!

    Well, the table has six columns, 4 are part of the PK and rest 2 are to server other business purpose. Because of those 2 extra columns I am finding it hard to delete the duplicate entries.

    dollar

    Originally posted by Tank
    If there is no other columns in the new table than the
    key columns, select distinct * followed by an insert, is by
    far the easiest approach.
    If there are columns to be preserved,
    apart from the PK, you have to make an informed decision, on
    which rows to delete - supposedly according to some rule.
    If the remaining information is of no consequence, again the
    select distinct * is the easiest approach.

    BOW

  6. #6
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by yogeshkansal
    HTH:

    db2 => select rownumber() over (partition by deptno) from department

    1
    --------------------
    1
    1
    1
    1
    1
    1
    1
    1
    1
    2

    10 record(s) selected.

    db2 => create view tempv(rno) as select rownumber() over (partition by
    deptno) f
    rom department
    DB20000I The SQL command completed successfully.

    db2 => delete from tempv where rno > 1
    DB20000I The SQL command completed successfully.
    db2 => select * from department

    DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION
    ------ ----------------------------- ------ -------- ----------------
    A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 -
    B01 PLANNING 000020 A00 -
    C01 INFORMATION CENTER 000030 A00 -
    D01 DEVELOPMENT CENTER - A00 -
    D11 MANUFACTURING SYSTEMS 000060 D01 -
    D21 ADMINISTRATION SYSTEMS 000070 D01 -
    E01 SUPPORT SERVICES 000050 A00 -
    E11 OPERATIONS 000090 E01 -
    E21 SOFTWARE SUPPORT 000100 E01 -

    9 record(s) selected.

    db2 =>
    db2 => select rownumber() over (partition by deptno order by mgrno) from
    department

    1
    --------------------
    1
    --------------------
    1
    1
    1
    1
    1
    1
    1
    1
    1

    9 record(s) selected.
    Works nicely!

    Adding that one to the list...
    --
    Jonathan Petruk
    DB2 Database Consultant

  7. #7
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Nice!

Posting Permissions

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