通过加入，分组和拥有进行更新 [英] Update with a Join, Group By, and Having

问题描述

select语句执行时没有错误或警告。

The select statement executes with no errors or warning.

更新语句引发错误：

关键字'group'附近的语法不正确。

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?

此派生表有效

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]

我将在几天内留下任何意见或答案在更好的方法上，然后删除即可。这种方法在现有的SO答案中。

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.

推荐答案

您原始的更新语句具有GROUP BY和HAVING，这是UPDATE语句语法所不允许的。这是语法图的链接：更新（Transact-SQL）。

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).

您的第二个版本将GROUP BY和HAVING作为派生表的一部分，允许使用 。

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.

顺便说一句，我同意@bluefeet：CTE代替派生表将使您的更新更易于阅读和理解。虽小，但在维护的便利性上却有很大的不同。

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.

这篇关于通过加入，分组和拥有进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！