Sql-server – Update Grouped records

duplicationsql serverupdate

I have the following query that returns duplicate values:

  SELECT 
    [t].[Field1], [t].[Field2], COUNT([t].[Field2]) 
  FROM [dbo].[Table1] AS [t]
  WHERE [t].[Field2] <> ''
  GROUP BY [t].[Field1], [t].[Field2]
  HAVING COUNT([t].[Field2]) > 1

Given that statement, I'd like to write an update statement to clear Field2 under these conditions. How would I write this?

Best Answer

You can do this much simpler using a CTE:

;WITH x AS   
(
  SELECT 
    Field1, Field2, 
    c = COUNT(*) OVER(PARTITION BY Field1, Field2) 
  FROM dbo.Table1
  WHERE Field2 <> ''
)
UPDATE x SET Field2 = '' WHERE c > 1;

Or with a more cumbersome (and potentially less efficient, did not test) self-join:

UPDATE t SET Field2 = ''
FROM dbo.Table1 AS t
INNER JOIN (SELECT Field1, Field2, c = COUNT(Field2)
            FROM dbo.Table1
            WHERE Field2 <> ''
            GROUP BY Field1, Field2
            HAVING COUNT(Field2) > 1) AS x
 ON x.Field1 = t.Field1
 AND x.Field2 = t.Field2;
Related Question