用另一个表中的计数更新列：我的查询不起作用。我得到一个错误

Question

记分卡表：

库存表：

我想写一个查询更新记分卡表与成功计数从库存表上定义的lsp

这是我在SQL服务器中的查询：

UPDATE [Scorecard] 
SET Success = (SELECT COUNT([INVENTORY].[Status]) 
               FROM [INVENTORY]  
               WHERE [INVENTORY].STATUS = SUCCESS 
                 AND [INVENTORY].[werks] = [Scorecard].[werks])

Answer 1

这是一种可能的方式。我已经清点了werk的分区。

 SELECT 
  COUNT (1) OVER (PARTITION BY Werks ORDER by   lsp ) AS [num]
  ,Werks
 INTO #Temp_A
 FROM       [INVENTORY] 
 WHERE [Status] ='SUCCESS'


UPDATE A
   SET Success = B.num
 FROM  [Scorecard] A
 INNER JOIN  #Temp_A B ON A.Werks =  B.Werks 

DROP TABLE #Temp_A

Answer 2

正如评论中提到的，有一个很大的设计问题，但是要回答你的问题：

UPDATE [Scorecard] 
SET Success = (
        SELECT COUNT(*) 
        FROM [INVENTORY]  
        WHERE [INVENTORY].STATUS = 'SUCCESS'
        AND [INVENTORY].[werks] = [Scorecard].[werks]
        AND [INVENTORY].[lsp] = [Scorecard].[lsp]
              )