Remove duplicate rows where values are the same

claimRemark        masterClaimID      medinfoReferralCode       medinfoCompany      accession_no      medinfoApptno
LM3598523CT      3215274                      KNI320                               RMR                              1851300                      1669308
LM3599207XA      3215276                      SCH091                               RMR                              1851298                      1669307
LM3599207XA      3219463                      SCH091                               RMR                              1669307                      1669307

need sql query that removes only the duplicate claimremark values where accession_no <> medinfoapptno.

Results:
claimRemark        masterClaimID      medinfoReferralCode       medinfoCompany      accession_no      medinfoApptno
LM3598523CT      3215274                      KNI320                               RMR                              1851300                      1669308
LM3599207XA      3219463                      SCH091                               RMR                              1669307                      1669307
DaneanAsked:
Who is Participating?
 
awking00Connect With a Mentor Commented:
delete from Daneandata2
where accession_no != medinfoApptno
and claimRemark in
(select claimRemark from
 (select claimRemark, count(*)
  from Daneandata2
  group by claimRemark
  having count(*) > 1)
);
0
 
DaneanAuthor Commented:
I found the solution:

DELETE FROM Daneandata2
where Daneandata2.claimRemark = Daneandata2.claimRemark and Daneandata2.accession_no = Daneandata2.medinfoApptno

thanks.
0
 
awking00Commented:
I believe your solution will actually delete the record where the accession_no equals the medinfoApptno so the results would be -
claimRemark        masterClaimID      medinfoReferralCode       medinfoCompany      accession_no      medinfoApptno
 LM3598523CT      3215274                      KNI320                               RMR                              1851300                      1669308
 LM3599207XA      3219463                      SCH091                              RMR                              1851298                      1669307
I'm still testing a solution and will get back to you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.