Steven Lemmens Steven Lemmens - 6 months ago 40
SQL Question

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?

Answer Source

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) 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download