I have the following query that returns duplicate values:

    [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?


You can do this much simpler using a CTE:

;WITH x AS   
    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;
  • How do you select from X after the update when using that CTE method? I'm thinking a temp table will be necessary. – Michael Z. May 26 '16 at 23:42
  • @MichaelZ. Why do you need to "select from X after the update"? You can use the OUTPUT clause but I'd need to understand what you're trying to select and why... – Aaron Bertrand May 27 '16 at 3:28
  • Yeah I guess you wouldn't need to. I'm just confusing myself. I don't think I've used OUTPUT before so I'll look it up and see if it's useful for me. – Michael Z. May 27 '16 at 4:08

Found the answer. Use a temp table and then inner join:

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

 UPDATE [dbo].[Table1]
 SET [Field2] = ''    
 FROM [dbo].[Table1] AS [t]
 INNER JOIN [#TempTable] AS [tt] 
    ON [tt].[Field1] = [t].[Field1] AND [tt].[Field2] = [t].[Field2]

 DROP TABLE [#TempTable]
  • FYI, INNER JOIN and JOIN are the same thing in SQL Server. A little less typing. – Michael Z. May 26 '16 at 23:46
  • @MichaelZ. For most people, clarity > saving a few keystrokes. – Aaron Bertrand May 27 '16 at 3:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.