I need to find using a CTE all Providers that belong to Multiple provider groups and I'm having a difficult time doing so.
Here's how I'd like for this to work. Find a provider, if the provider belongs to one group, show it. If the provider belongs to multiple groups, then update all of the provider groups for this provider to null.
WITH theResultSet AS
SELECT DISTINCT(ProviderKey) AS col1 FROM ##tmpProviderGrp_RAMember
FROM ##tmpProviderGrp_RAMember AS a
JOIN ##tmpProviderGrp_RAMember as b ON a.ProviderKey = b.ProviderKey
set IDA_ProviderGroupKey= NULL
WHERE cnt > 1;