Find duplicate records in two group of data in one sql server table

Question

I have one sql server (2008) table containing group of data.

Source    PersonId    Date      Description   Code     IsDup
------    --------    -----     -----------   ------   ----- 
EHR       1234        1/1/2012  Fever         120.12
EHR       1234        6/1/2012  Fracture      101.00
EHR       1234        11/4/2012 Hypertension  223.15
RAM       1234        1/1/2012  Fever         120.12              <-- Duplicate
RAM       1234        6/1/2012  Fracture      101.00              <-- Duplicate
RAM       1234        4/1/2012  Diabetic      601.00
TAR       1234        2/1/2012  Asthma        456.00
TAR       1234        1/1/2012  Fever         120.12              <-- Duplicate

I need to compare the data between the different groups. "EHR" being the master group, I need to check if any other group has data exactly matching that in "EHR" master group within the table. And then it should update the IsDup column with 1.

Expected Result:

Source    PersonId    Date      Description   Code     IsDup
------    --------    -----     -----------   ------   ----- 
EHR       1234        1/1/2012  Fever         120.12
EHR       1234        6/1/2012  Fracture      101.00
EHR       1234        11/4/2012 Hypertension  223.15
RAM       1234        1/1/2012  Fever         120.12     1
RAM       1234        6/1/2012  Fracture      101.00     1
RAM       1234        4/1/2012  Diabetic      601.00
TAR       1234        2/1/2012  Asthma        456.00
TAR       1234        1/1/2012  Fever         120.12     1

I know how to check for duplicates within the table but not sure how can we do comparison keeping one group static.

I got this from one of the stackoverflow thread to identify dups but how to add grouped comparision:

with x as   (select  *, rn = row_number()
            over(PARTITION BY [PersonId], [Date], [Description], [Code] order by [PersonId], [Date], [Description], [Code])
            from    Results)

select * from x
where rn > 1

http://stackoverflow.com/questions/3362038/what-is-self-join-and-when-would-you-use-it — RandomUs1r, Mar 15 '13 at 16:11

score 2 · Accepted Answer · answered Mar 15 '13 at 16:14

2

You can update your table using self join :

update r1 set isDup = 1
from results r1 join results r2 on 
     r1.PersonId = r2.PersonId and r1.Date = r2.Date and 
     r1.Description = r2.Description and r1.Code = r2.Code 
where r1.Source <> 'EHR' and r2.Source = 'EHR'

answered Mar 15 '13 at 16:14

Grisha Weintraub

7,803
1
25
45

Thanks, it worked as expected. Lamak's solution was great too but we prefer self joins over exists. – Sri Reddy Mar 15 '13 at 18:19

Lamak · Answer 2 · 2013-03-15T16:21:01.557

1

This should do:

UPDATE A
SET IsDup = 1 
FROM YourTable A
WHERE [Source] != 'EHR'
AND EXISTS (SELECT 1 FROM YourTable
            WHERE [Source] = 'EHR'
            AND PersonId = A.PersonId 
            AND [Date] = A.[Date]
            AND Description = A.Description
            AND Code = A.Code)

Here is a demo for you to try.

edited Mar 15 '13 at 16:21

answered Mar 15 '13 at 16:14

Lamak

69,480
12
108
116

Thanks Lamak for your solution and the demo. I didn't know that there is a sqlfiddle too. Thanks for educating me. In past we had issues with EXISTS so our DBA prefer self join. – Sri Reddy Mar 15 '13 at 18:25
@kuul13 yeah, sqlfiddle is a great tool. Well, I don't know what "issues" your DBA had with `EXISTS` in the past, but I think that it was probably a misunderstanding on how it works. Just to keep with the "educating" part, I suggest you (and your DBA) take a look at this blog post: http://explainextended.com/2009/06/16/in-vs-join-vs-exists/ – Lamak Mar 15 '13 at 18:30

score 0 · Answer 3 · answered Mar 15 '13 at 16:18

Try this:

;With rootQuery as 
(
Select  SOURCE, PersonId, Date, Description, Code
From    MedicalHistory 
Where   Source = 'EHR'
)
Update mhd
Set IsDuplicate = 1
From    rootquery mh 
Join    MedicalHistory mhd on mh.PersonId = mhd.PersonId
Where   mh.Description = mhd.Description
And     mh.Code = mhd.Code
And         mh.Date = mhd.Date
And     mhd.Source != 'EHR'

score 0 · Answer 4 · answered Apr 24 '13 at 06:39

Try this please..

   update tab
   set tab1.isDup=1
   from table1 tab1, table1 tab2
   where 
   tab1.PersonId=tab1.PersonId and
   tab1.Date=tab2.Date and
   tab1.desription=tab2.desription and
   tab1.Code=tab2.Code and
   tab1.Source != tab2.source

Find duplicate records in two group of data in one sql server table

4 Answers4