Solved

sql to display and delete duplicates

Posted on 2014-09-05
12
105 Views
Last Modified: 2014-09-09
I am trying to create a recordset that displays duplicates and also then a SP to delete them.

The table name is "Atts"

Fields are:

Select * from Atts
where caseid and userid (ARE DUPLICATES).

In other words, I want to see the rows in which caseid and userid are the same in row # 1 and row # 2, althought I want to display that only once, not 2 or 3 times.

Then to clean it up I want to run a SP to delete the duplicate items (or 3 or 4) and leave only one.

Example

Name     |   caseid   |    userid   |   other

Aleks       |  1001      |    323        |   XXXXX
Aleks       |  1001       |    323        |  XXXXX
Maria       |   1001            |    433         |
John          |  4324     |  23233  |   YYYY

The first two are duplicates, I want to display that result once, then the SP to delete the exceeding and leave just one. The example shows one but it would have 4 or 5 entries with the same values under "Caseid"  and "userid"


Delete from Atts
where caseid and userid (ARE DUPLICATE)  --- But only the exceeding ones, so to leave one entry

Hope that makes sense.
0
Comment
Question by:amucinobluedot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 1

Accepted Solution

by:
Subramani N earned 500 total points
ID: 40306671
Here is the SP.
CREATE PROCEDURE SP_DEL_DUPS
AS
BEGIN

SELECT
   DISTINCT caseid,userid
FROM
   Atts
GROUP BY
   caseid,userid
HAVING
   COUNT(1) > 1

WITH TMP_DUPS AS(
   SELECT caseid, userid, rownum = ROW_NUMBER()OVER(PARTITION BY caseid, userid ORDER BY caseid, userid)
   FROM Atts
)
DELETE FROM TMP_DUPS WHERE rownum > 1

END
GO

Open in new window

0
 
LVL 1

Assisted Solution

by:Subramani N
Subramani N earned 500 total points
ID: 40306682
Here is another example.

http://support.microsoft.com/kb/139444
0
 

Author Comment

by:amucinobluedot
ID: 40306689
Ill try the SP above see how that works. Will get back to you this weekend.
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

Author Comment

by:amucinobluedot
ID: 40306698
I would also need the sql to display the dups. I tried:

SELECT
   DISTINCT caseid,userid
FROM
   Atts
GROUP BY
   caseid,userid
HAVING
   COUNT(1) > 1


But that displays the unique ones. I need user to review how many records will be deleted, then to test Ill substract and make sure it was done properly
0
 
LVL 1

Assisted Solution

by:Subramani N
Subramani N earned 500 total points
ID: 40306718
Try this.
SELECT
   caseid,userid,count(1)-1 as 'RowsToBeDeleted'
FROM
   Atts
GROUP BY
   caseid,userid
HAVING
   COUNT(1) > 1

Open in new window

0
 

Author Comment

by:amucinobluedot
ID: 40306730
Looks great ! Ill take it the SP will work so Ill assign the points already.
0
 

Author Comment

by:amucinobluedot
ID: 40306766
I've requested that this question be closed as follows:

Accepted answer: 0 points for amucinobluedot's comment #a40306698
Assisted answer: 250 points for Subramani N's comment #a40306671
Assisted answer: 250 points for Subramani N's comment #a40306718

for the following reason:

:)
0
 
LVL 1

Assisted Solution

by:Subramani N
Subramani N earned 500 total points
ID: 40306741
You dont have to raise a Close request. You just click on the Comment and select as solution and provide the points, it will be closed automatically and moved from the queue.
0
 
LVL 1

Assisted Solution

by:Subramani N
Subramani N earned 500 total points
ID: 40306767
Please accept the answer and submit.
0
 

Author Comment

by:amucinobluedot
ID: 40306797
That was not a direct answer to my specific query.  Before I had a chance to even start working on it I got an answer to my specific request, which is much more useful than a link to a page I could have googled. Not sure if that should get any points. Ill leave that to the EE moderator.
0
 

Author Closing Comment

by:amucinobluedot
ID: 40311982
Sorry I thought I had already done this.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Because your company can’t afford for you to make SEO mistakes, you’ll want to ensure you’re taking the right steps each and every time you post a new piece of content. This list of optimization do’s and don’ts can help you become an SEO wizard.
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn how to count occurrences of each item in an array.

688 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