2

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"

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jim G
  • 51
  • 6

1 Answers1

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]
rwking
  • 1,032
  • 6
  • 18
  • 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 – Jim G Sep 04 '15 at 16:58
  • See update... "set t.Flag = 'Y' " – rwking Sep 04 '15 at 17:00
  • 1
    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 – Jim G Sep 04 '15 at 17:19
  • Awesome! If it helped, feel free to accept it as the answer. – rwking Sep 04 '15 at 17:20