-- Retrieve duplicate 'Heads' [first unique instance of a hash]
-- IGNORE_DUP_KEY = ON
DECLARE @Duplicates AS TABLE([Id] INT UNIQUE WITH(IGNORE_DUP_KEY = ON),[RootPid] INT,[DuplicatePid] INT,[Duplicates] BIT DEFAULT(0),[Hash] VARCHAR(50))
INSERT INTO @Duplicates
    SELECT [Id],[RootPid],[Id],1,[Hash]
    FROM [dbo].[Inventory]
    WHERE  [Id] IN
        (
            SELECT MIN([Id])
            FROM [dbo].[Inventory]
            WHERE [Hash] <> '' AND                  -- Exclude items w/no hash (never parent or dup)
            ([Lvl]=0 OR([Lvl] =1 AND [FileCat]=1 )) -- Constrain to L1 or StoreLevel emails
            GROUP BY [Hash] HAVING Count([Hash])>1
        )

-- Expand subsequent instances of the hash [from <=L1]
INSERT INTO @Duplicates
SELECT i.[Id],i.[RootPid],d.[DuplicatePid],0,i.[Hash]
FROM [Inventory] i INNER JOIN @Duplicates d
ON i.[Hash] = d.[Hash]
AND ([Lvl]=0 OR([Lvl] =1 AND [FileCat]=1 ))


-- Incorporate attachments
INSERT INTO @Duplicates
SELECT i.[Id],i.[RootPid],d.[DuplicatePid],0,i.[Hash]
FROM [Inventory] i INNER JOIN @Duplicates d ON i.[RootPid] = d.[Id]


-- Incorporate attachments + Update
UPDATE i
SET i.[Duplicates] = d.[Duplicates], i.[DuplicatePid] = d.[DuplicatePid]
FROM [Inventory] i INNER JOIN @Duplicates d ON i.[Id] = d.[Id]