SQL Server : how to use alias in update statement?

Question

我想知道以下查询:

   UPDATE statisticsTable
   SET Value = (select count(*) 
                FROM OtherTable o
                WHERE o.UserId = UserId ) <-- this is the part that concerns me
   WHERE id in (1,2,3) 

SQL Server 如何知道第二个“UserId”字段来自 statisticsTable 而不是来自 OtherTable ？为什么我不能为 statstable 提供一个别名(例如“stat”)来阐明我想要在哪里获取 UserId ？或者有什么办法吗？

Answer 1

SQL Server 支持使用联接进行更新。
这意味着您可以像这样编写查询:

UPDATE s
SET Value = d.NumOfRows
FROM statisticsTable s
INNER JOIN
(
     SELECT UserId, COUNT(*) As NumOfRows
     FROM OtherTable
     GROUP BY UserId
) d ON s.UserId = d.UserId
WHERE id in (1,2,3)