Update Grouped records 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] &lt;&gt; ''
  GROUP BY [t].[Field1], [t].[Field2]
  HAVING COUNT([t].[Field2]) &gt; 1


Given that statement, I'd like to write an update statement to clear Field2 under these conditions.  How would I write this?
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 &lt;&gt; ''
)
UPDATE x SET Field2 = '' WHERE c &gt; 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 &lt;&gt; ''
            GROUP BY Field1, Field2
            HAVING COUNT(Field2) &gt; 1) AS x
 ON x.Field1 = t.Field1
 AND x.Field2 = t.Field2;