Find duplicates based on two columns

Question

I need to be able to find some duplicate rows so I can update a deleted flag on all but one of them.

Say the table is called tblMR, I need to find duplicates that share a LoadManifestID and a VINid, I then need to be able to select one and do an update. Each have a unique column of EntryLineNo

I haven't tried anything yet as I have no idea where to start.

please read [**How to ask**](http://stackoverflow.com/help/how-to-ask) And [**How to create a Minimal, Complete, and Verifiable example.**](http://stackoverflow.com/help/mcve) this is a great place to start http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Juan Carlos Oropeza, Nov 24 '15 at 16:53
How about starting by finding "duplicates"? If they all have a unique EntryLineNo which one would you want to update? — Sean Lange, Nov 24 '15 at 16:53

score 1 · Accepted Answer · answered Nov 24 '15 at 16:56

You really need to post more details to make this more clear and easier for the people helping but I think this is pretty close to what you are looking for.

with FindDupes as
(
    select LoadManifestID
        , VINid
        , ROW_NUMBER() over(partition by LoadManifestID, VINid order by EntryLineNo) as RowNum
    from tblMR
)

update m
set IsDeleted = 1
from tblMR m
join FindDupes d on d.LoadManifestID = m.LoadManifestID 
                and d.VINid = m.VINid
where d.RowNum > 1

score 0 · Answer 2 · answered Nov 24 '15 at 17:02

Where you should start?

Try querying all EntryLine.

SELECT * 
FROM EntryLine

If that works, proceed to find the number of duplicates for all EntryLine.

SELECT LeadManifestID, VINid, count(*)
FROM EntryLine

If that works, try finding a deterministic EntryLineNo for each group of EntryLine.

SELECT MIN(EntryLineNo), LeadManifestID, VINid, count(*)
FROM EntryLine

Once that's done, you need to find all rows that are duplicate but don't share the preselected EntryLineNo.

SELECT * 
FROM EntryLine e INNER JOIN (
    SELECT MIN(EntryLineNo) AS KeptNo, LeadManifestID, VINid, count(*)
    FROM EntryLine
) e2 ON e.VINid = e2.VINid AND e.LeadManifestID = e2.VINid AND e2.KeptNo != e.EntryLineNo

Then, you simply have to update the table

UPDATE
    e
SET
    toBeDelted = true
FROM
    SELECT * 
    FROM EntryLine e INNER JOIN (
        SELECT MIN(EntryLineNo) AS KeptNo, LeadManifestID, VINid, count(*)
        FROM EntryLine
    ) e2 ON e.VINid=e2.VINid AND e.LeadManifestID=e2.VINid AND e2.KeptNo!=e.EntryLineNo

This is a pretty wasteful method though, but it is definitely a good way to start. From then, you could look into ROW_NUMBER for instance and simplify the process so much.

Find duplicates based on two columns

2 Answers2