September 16, 2015 at 10:07 am
Select MemberNbr, EligYear,EligMonth, count(*) FROM [MemberMonth] GROUP BY MemberNbr, EligYear,EligMonth HAVING COUNT(*) > 1
This shows that there are duplicates.
Can you help me write the delete stmt that will eliminate the duplicates. I don't care which record is kept and which is deleted.
September 16, 2015 at 11:00 am
Here's an article about it:
http://www.sqlservercentral.com/blogs/vivekssqlnotes/2013/12/29/deleting-duplicate-rows-using-cte/
September 16, 2015 at 11:13 am
mw112009 (9/16/2015)
Select MemberNbr, EligYear,EligMonth, count(*) FROM [MemberMonth] GROUP BY MemberNbr, EligYear,EligMonth HAVING COUNT(*) > 1
This shows that there are duplicates.
Can you help me write the delete stmt that will eliminate the duplicates. I don't care which record is kept and which is deleted.
Run this to look at the data and verify the duplicate data:
with basedata as (
select
*,
rn = row_number() over (partition by MemberNbr, EligYear, EligMonth order by (select null))
from
dbo.MemberMonth
)
select * from basedata;
Run this to delete the duplicate data if the above is valid:
with basedata as (
select
*,
rn = row_number() over (partition by MemberNbr, EligYear, EligMonth order by (select null))
from
dbo.MemberMonth
)
delete from basedata where rn > 1;
September 16, 2015 at 11:14 am
pietlinden (9/16/2015)
Make it easier for people to get to the article:
http://www.sqlservercentral.com/blogs/vivekssqlnotes/2013/12/29/deleting-duplicate-rows-using-cte/
September 16, 2015 at 11:35 am
Thank you dear!
Works like a charm!
Good job!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply