Search code examples
sqlsql-serversql-update

SQL Server: how to use alias in update statement?

I'm wondering about the following query : 

   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)

How does SQL Server know that the second "UserId" field comes from statisticsTable and not from OtherTable ? Why can't I give an alias to statisticstable like 'stat' to clarify where I want to get that UserId ? Or is there a way?

Solution

  • SQL Server supports updates using joins.
    This means you can write your query like this:

    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)