Update with a Join, Group By, and Having

Question

The select statement executes with no errors or warning.

The update statement throws an error:
Incorrect syntax near the keyword 'group'.

select [sSVsys].[textUniqueWordCount], count(*) as [actCount] 
from [docSVsys]  as [sSVsys]with (nolock)
join [FTSindexWordOnce] with (nolock)
  on [sSVsys].[sID] = [FTSindexWordOnce].[sID]
where [sSVsys].[sID] < 500000
group by [sSVsys].[sID], [sSVsys].[textUniqueWordCount] 
having [sSVsys].[textUniqueWordCount] <> count(*)

update [sSVsys]
set [sSVsys].[textUniqueWordCount] = count(*) 
from [docSVsys]  as [sSVsys]with (nolock)
join [FTSindexWordOnce] with (nolock)
  on [sSVsys].[sID] = [FTSindexWordOnce].[sID]
where [sSVsys].[sID] < 500000
group by [sSVsys].[sID], [sSVsys].[textUniqueWordCount] 
having [sSVsys].[textUniqueWordCount] <> count(*)

If the answer is to join to a derived table then I can figure that out.
Do I have a syntax error with the existing update?

This derived table worked

update [docSVsys] 
set [docSVsys].[textUniqueWordCount] = [WordOnce].[actCount]
from [docSVsys]
join 
(   select [FTSindexWordOnce].[sID], count(*) as [actCount]
    from   [FTSindexWordOnce] with (nolock)
    -- where  [FTSindexWordOnce].[sID] < 1500000
    group by [FTSindexWordOnce].[sID]  ) as [WordOnce]
 on [docSVsys].[sID] = [WordOnce].[sID]
and [docSVsys].[textUniqueWordCount] <> [WordOnce].[actCount]

I will leave this up for a couple days for any comments or answers on a better approach and then just delete. This approach is in an existing SO answer.

Typically I would use a subquery with the aggregate or a CTE. — Taryn, Mar 28 '13 at 17:42

score 5 · Accepted Answer · answered Apr 07 '13 at 21:51

Your original update statement has GROUP BY and HAVING, which are not allowed in the UPDATE statement syntax. Here's a link to a syntax diagram: UPDATE (Transact-SQL).

Your second version has the GROUP BY and HAVING as part of a derived table, which is allowed.

So, yeah: you did have a syntax error.

Incidentally, I agree with @bluefeet: a CTE in place of a derived table would make your update easier to read and understand. A small thing, but it can make a big difference in ease of maintenance.

Bluefeet did not recommend a CTE over a sub-query and I fail to see how a CTE can make a big difference on maintenance. But still thanks for the answer. +1 — paparazzo, Apr 08 '13 at 00:30

Update with a Join, Group By, and Having

1 Answers1

Linked