0

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?

0

2 Answers 2

5

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;
3
  • 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.
    Commented May 26, 2016 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... Commented May 27, 2016 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.
    Commented May 27, 2016 at 4:08
1

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

  SELECT [t].[Field1], [t].[Field2], COUNT([t].[Field2]) AS Counted
  INTO
    #TempTable
  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]
2
  • FYI, INNER JOIN and JOIN are the same thing in SQL Server. A little less typing.
    – Michael Z.
    Commented May 26, 2016 at 23:46
  • @MichaelZ. For most people, clarity > saving a few keystrokes. Commented May 27, 2016 at 3:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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