RE How to delete duplicate rows in the table

[Gokul Sagi]

Hi Kishore ,

Heres the example for your query

C:\>db2 "create table data (col1 int,col2 char(5))"
DB20000I The SQL command completed successfully.

C:\>db2 "insert into data values (1,'hai'),(2,'how'),(3,'him')"
DB20000I The SQL command completed successfully.

C:\>db2 "insert into data values (1,'hai'),(2,'how'),(3,'him')"
DB20000I The SQL command completed successfully.

C:\>db2 "insert into data values (1,'hai'),(2,'how'),(3,'him')"
DB20000I The SQL command completed successfully.

C:\>db2 "insert into data values (1,'hai'),(2,'how'),(3,'him')"
DB20000I The SQL command completed successfully.

C:\>db2 "insert into data values (1,'hai'),(2,'how'),(3,'him')"
DB20000I The SQL command completed successfully.

C:\>db2 "select * from data"

COL1 COL2
----------- -----
1 hai
2 how
3 him
1 hai
2 how
3 him
1 hai
2 how
3 him
1 hai
2 how
3 him
1 hai
2 how
3 him

15 record(s) selected.

C:\>db2 "delete from (select row_number() over ( partition by col1 order by col2 desc ) as rn from
data ) where rn>1"
DB20000I The SQL command completed successfully.

C:\>db2 "select * from data"

COL1 COL2
----------- -----
1 hai
2 how
3 him

3 record(s) selected.

Thanks & Regards,

Gokul.Sagi
Miracle Software Systems pvt.ltd
Web:www.miraclesoft.com
contactno:+91-9290063929
email:gs...@miraclesoft.com

------- Original Message -------
From : malla kishore[mailto:mallak...@gmail.com]
Sent : 8/28/2007 7:23:19 AM
To : mirac...@googlegroups.com
Cc :
Subject : RE: Re: How to delete duplicate rows in the table

Hi Gokul,

Nice to have u r reply..

Can you please breif that with example

Advance Thanks

On 8/28/07, Gokul Sagi <gs...@miraclesoft.com> wrote:
>
>
>
> Hi
>
> This command is helpful for deleting the duplicate rows in the given
> example and it worked
> succesfully.
>
> db2 " delete from (select row_number() over (partition by col1 order by
> col2 desc ) as rn from data
> data ) where rn >1 "
>
>
>
> Thanks & Regards,
>
> Gokul.Sagi
> Miracle Software Systems pvt.ltd
> Web:www.miraclesoft.com
> contactno:+91-9290063929
> email:gs...@miraclesoft.com
>
>
> ------- Original Message -------
> From : malla kishore[ mailto:mallak...@gmail.com]
> Sent : 8/28/2007 6:07:06 AM
> To : mirac...@googlegroups.com
> Cc :
> Subject : RE: How to delete duplicate rows in the table
>
> Hi Team,
>
> Can any one suggest the way's to delete the duplicate rows in a table
>
> example
> i have
>
> Table data
>
> col1 col1
>
> 1 hai
> 2 how
> 1 hai
>
> i want
>
>
> col1 col1
>
>
> 1 hai
> 2 how
>
>
>
> waiting for your replies
> Thanks
> kishore
>
>
>
>
>
> >
>

--
కిశోర్ మల్ల

[malla kishore]

 

Thanks and Good Work Gokul Keep it up..

 

Keep Posting ..

 

This inspired me a lot and i will sure hope that all other team mates will also post there replies and now on wards i will try posting one topic each day .

 

I HOPE THERE WILL BE A POSITIVE RESPONCE.

 

Thanks

Kishore

 

[Satish Mullapudi]

Hi All,

 

I guess this example along with the expln. would resolve ur queries reg. this stmt.

 

TABLE - Emp
EMPNO  NAME       SALARY
----------- -----------------   ------------
000010 CHRISTINE   52750.00
000020 MICHAEL      41250.00
000030 SALLY          38250.00
000050 JOHN            40175.00
000060 IRVING          32250.00
000020 MICHAEL       41250.00
000050 JOHN            40175.00

In this EMP table, 2 of the emp nos r repeating which needs to be deleted.
So, here is the query for that:
db2  DELETE FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Empno) AS e FROM Emp ) WHERE e >1

 

Here is the o/p of the query with the duplicate records removed:

EMPNO  NAME       SALARY
----------- -----------------   ------------
000010 CHRISTINE    52750.00
000020 MICHAEL      41250.00
000030 SALLY          38250.00
000050 JOHN            40175.00
000060 IRVING          32250.00

 

Expln:

 

The flow of query execution goes like this:

Step1: The PARTITION clause first creates partitions within the table by the Col. name specified ( here it is Empno ). Here in our case it creates 5 partitions , each for one EMPNO. So, all these 7 Empnos will be partitioned into 5 groups.

Step2: Then SELECT all the rows using their resp. row_number from each partition. So in our case, u'll get as 2 of the partitions are having more than 1 record each.

Step3: DELETE all the records in each which are having row_number >1. In our case,  it goes like this

PartitionA       Rownumber

000010                  1

PartitionB       Rownumber

000020                  1

000020                  2

PartitionC       Rownumber

000030                  1

PartitionD       Rownumber

000040                  1

000040                  2

PartitionE       Rownumber

000050                  1

 

So , these 2 records will be deleted.

 

This is how the operation goes.

 

Attached is the Access Plan of the same.

Feel free to contact me if you have further queries

IBM Certified Database Associate,
IBM Information Management (DB2 UDB),  
Miracle Software Systems Inc.
email: smull...@miraclesoft.com
Mobile: +919923020321

[malla kishore]

Thanks satish for the explanation

 

FInallly coming to solution

 

db2  DELETE FROM (SELECT ROW_NUMBER() OVER (PARTITION BY <colname>) as e  FROM <tabname>  WHERE  e>1..

 

Thank you all for you wonderful support especially for GOKUL  initiative and hope this will continue.. 

 

Thanks

Kishore