Need to update one column for records where another value is duplicative in SQL Server [duplicate]

Question

I am VERY rusty with my SQL, it's been a few years, but I need to write a query to fix something in a table I have.

There are erroneous duplicates where not every column is the same, but I know at least one is.

So I have this query that works:

SELECT 
    [IntMsgID], [SortDate], COUNT(*)
FROM 
    [databasename].[dbo].[tblDoc]
GROUP BY
    [IntMsgID], [SortDate]
HAVING 
    COUNT(*) >= 2 
    AND [IntMsgID] IS NOT NULL

It identifies the documents in question. What I need to do, then, is take the results from that and update another field simply with the value of Y or 1.

I've done searching and it seems any query I've tried to plug in fails, such as

UPDATE [databasename].[dbo].[tblDoc] AS t 
INNER JOIN 
    (SELECT [IntMsgID] msgid 
     FROM [databasename].[dbo].[tblDoc] 
     GROUP BY [IntMsgID]) t1 ON t.[IntMsgID] = t1.[IntMsgID]
SET [JG_SQLDupe] = 'Y'

I get syntax errors at "AS" and "INNER" and "t1"

Thanks, I'll look through that thread. I had not found it on my own searching — Jim G
– Jim G, Commented Sep 4, 2015 at 17:05

rwking · Accepted Answer · 2015-09-04 16:59:59Z

0

I would use a CTE to achieve this:

;with updts as (SELECT [IntMsgID], [SortDate], count(*)
                FROM [databasename].[dbo].[tblDoc]
                Group By [IntMsgID], [SortDate]
                HAVING count(*) >= 2 AND [IntMsgID] is not null)
 update t
 set t.Flag = 'Y' 
 from [databasename].[dbo].[tblDoc] t inner join 
      updts u on t.[IntMsgID] = u.[IntMsgID] 
             and t.[SortDate] = u.[SortDate]

edited Sep 4, 2015 at 16:59

answered Sep 4, 2015 at 16:54

rwking

1,0326 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jim G Over a year ago

Sorry, I should have been clear, either just Y or 1 exclusively, there no reason I'd need both to ever show up. So we'll just say I only need the value Y to show up in my JG_SQLDupe field

rwking Over a year ago

See update... "set t.Flag = 'Y' "

Jim G Over a year ago

Yeah, made that one change and it's working. Thanks so much. Also I had to give aliases to the columns in the initial SELECT statement as it was throwing me an error for the first "updts" value without doing so

rwking Over a year ago

Awesome! If it helped, feel free to accept it as the answer.

Collectives™ on Stack Overflow

Need to update one column for records where another value is duplicative in SQL Server [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related