[MSSQL] How to delete duplicate rows with SQL

常常會遇到Delete重覆資料的問題，把專案的SP記錄下來。

ALTER PROCEDURE [dbo].[DeleteMembersRoleDuplicatedRows]
    
AS
BEGIN
--重覆的資料列，取pk id最小值的保留
create  table #to_delete 
(
    min_id int not null,
    MembersRoleMemberID int not null,
    MembersRoleOrgRoleID int not null,
    Repeat int not null
)

--records duplicate rows
insert into #to_delete(min_id,MembersRoleMemberID,MembersRoleOrgRoleID,Repeat)
SELECT     MIN(MembersRoleID) AS Min_ID, MembersRoleMemberID, MembersRoleOrgRoleID,
count(*) as Repeat
FROM         MembersRole
GROUP BY MembersRoleOrgRoleID, MembersRoleMemberID
HAVING      (COUNT(*) > 1)
ORDER BY MembersRoleMemberID


delete from MembersRole
where exists
(
    select * from #to_delete
    where 
    #to_delete.min_id <> MembersRole.MembersRoleID
    and #to_delete.MembersRoleOrgRoleID = MembersRole.MembersRoleOrgRoleID
    and #to_delete.MembersRoleMemberID = MembersRole.MembersRoleMemberID
)

END

Reference:
How to find duplicate rows with SQL
How to delete duplicate rows with SQL