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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DaneanAuthor Commented:
I found the solution:

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

thanks.
0
awking00Information Technology SpecialistCommented:
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
awking00Information Technology SpecialistCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.