更新语句中的SQL Server语法错误，但我看不到它

I'm getting a syntax error on this query, but I can't figure it out.

我在这个查询中遇到语法错误，但我无法弄明白。

Incorrect syntax near the keyword 'group'.

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

I believe its on the last group by, but I don't see whats wrong. Can anyone suggest how to correct this?

我相信它在最后一组，但我没有看到什么是错的。任何人都可以建议如何纠正这个？

UPDATE [NCLGS].[dbo].[CP_CustomerShipTo]
SET     TimesUsed = TimesUsed + B.NewCount
from [NCLGS].[dbo].[CP_CustomerShipTo] CST
INNER JOIN (    
    Select
        PKH.CompanyCode, 
        PKH.CompanyName, 
        PKH.Addr1, 
        PKH.Addr2, 
        PKH.City, 
        PKH.State, 
        PKH.Zip, 
        Count(recid) As NewCount
    from avanti_packingslipheader PKH 
    where pksdate > dbo.ufn_StartOfDay(DATEADD(d, -1, GETDATE() ) )   
    group by 
        PKH.CompanyCode, 
        PKH.CompanyName, 
        PKH.Addr1, 
        PKH.Addr2, 
        PKH.City, 
        PKH.State, 
        PKH.Zip 
) B 
ON CST.CustomerCode     =   B.CompanyCode
   AND CST.ShipToName       =   B.CompanyName
   AND CST.ShipToAddress1   =   B.Addr1
   AND CST.City             =   B.City
   AND CST.PostalCode       =   B.Zip

group by 
    PKH.CompanyCode, 
    PKH.CompanyName, 
    PKH.Addr1, 
    PKH.Addr2, 
    PKH.City, 
    PKH.State, 
    PKH.Zip

BACKGROUND - I'm trying to do an update statement with a Count(), but of course you can't use agg. functions in an update set statement, so I'm trying to use a subquery.

背景 - 我正在尝试用Count（）做一个更新语句，但当然你不能使用agg。函数在更新集语句中，所以我试图使用子查询。

3 个解决方案

#1

Try removing the last Group By. What exactly are you hoping this last group by will do?

尝试删除最后一个分组依据。你究竟希望这最后一组能做什么？

#2

You have already got GROUP BY inside the subselect, so what does the outer GROUP BY stand for?

你已经在subselect中有了GROUP BY，那么外部GROUP BY代表什么？

You can't reference an alias in a subselect from an outer GROUP BY. But in any event you can't use GROUP BY with an UPDATE statement, and that's what the error message is about.

您无法在外部GROUP BY的子选择中引用别名。但无论如何，您不能将GROUP BY与UPDATE语句一起使用，这就是错误消息的含义。

#3

Change the code to this:

将代码更改为：

update mytable set
mycolumn = mycolumn + (select x from ...);

#1