How to Delete Duplicate rows in a Table?

[nagumotu]

My table contains duplicate rows

case1: leave one row and delete other matching rows.

case2: Merge all duplicate rows and make one row. sum on perticular column(numeric) for those rows.

 

[blom0344]

Ideally your table should have a primary key to prevent this from happening..........
Create a database view using distinct option on all fields. Export the data from the view. Alter the table definition, truncate the table and load the export file.........

T. Blom
Information analyst
tbl@shimano-eu.com

 

[solanki123]

Hi Blom can explain me how to export,load
,truncate the table.
Can you please explain me with example?
---solanki123

 

[manoj77]

To eiliminate the duplicate rows :

1. create new table like old table

db2 create table new_table like old_table

2. put distinct data into it from old table

db2 insert into new_table select distinct * from old_table

3. drop old table and rename new table as old table

db2 drop table old_table

db2 rename new_table to old_table

regards,
Manoj

 

[solanki123]

Hi manoj
Your explaination is really very very helpful.
Thank you very much.

 

[solanki123]

Still I have a doubt that how is it possible to
have duplicate record in a table that has primry key.
I think this error is -803 but when and how duplicate records get inserted into table.