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

Hi Dollar

Have you tried:

Select distinct * from mytable

BOW

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.

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

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

Works nicely!

Adding that one to the list...

Nice!