How do I delete members from a group that have "hidden" group member historical

Question

I have a group that had group history turned on at one point then was turned back off. I need to delete members from the group but keep getting an error message that this Group Member is assigned to a Group Member Historical. How do I fix this?

Answer 1

1. You can run a query to see Group Member Historical data: (replace group id where the 64517 is listed).

   SELECT
       GMH.*
   FROM
       [GroupMemberHistorical] GMH
       INNER JOIN [GroupMember] GM ON GM.[Id] = GMH.[GroupMemberId]
   WHERE
       GM.[GroupId] = 64517
   
   

2. You would then use another query to delete those records, either manually entering the Ids or using a subquery.

   Example 1:

   DELETE FROM [GroupMemberHistorial] Where Id IN () -- Enter comma separated Ids
   

   Example 2:

   WITH Details as (
       SELECT
           GMH.Id
       FROM
           [GroupMemberHistorical] GMH
           INNER JOIN [GroupMember] GM ON GM.[Id] = GMH.[GroupMemberId]
       WHERE
           GM.[GroupId] = 64517
   )
   DELETE FROM [GroupMemberHistorical] WHERE Id IN (SELECT Id FROM Details)
   

The SQL included in this answer was provided by Brandon Meeks.