Solved

how to delete duplicate rows in db2 server

Posted on 2011-02-28
2
996 Views
Last Modified: 2012-05-11
i want the syntax to delete  the duplicate records of my table  in db2.
0
Comment
Question by:Sam2009
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 250 total points
ID: 34997501
assuming you have some pk column, and another column you can use to choose which record you want to keep, you can do:


with a as (
  select *, row_numbe() over(partition by pk_column1,pk_column2 order by case when value_column='value to keep' then -1 else 1 end asc) rown
 from your_table
)
delete from a where rown > 1
0
 

Author Comment

by:Sam2009
ID: 35043783
hello,

it worked!

thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
DB2 Drivers Windows Server - Database port number 4 439
db2 - u 3 229
The Event Monitor "DB2DETAILDEADLOCK" has reached its file 7 305
iSeries DB2 SQL - Query Missing Value 10 67
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now