SQL Server: how to use alias in update statement?

Question

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?

Is this working correctly? I usually do something like `WHERE o.UserId = statisticsTable.UserId` to be sure. — justiceorjustus, Aug 16 '17 at 14:10
https://stackoverflow.com/questions/4981481/how-to-write-update-sql-with-table-alias-in-sql-server-2008 — Daniel Stackenland, Aug 16 '17 at 14:11
Cant you have `UPDATE statisticsTable as s` and then have `WHERE o.UserID = s.UserId` in your subquery? I haven't tested this though. I've never had an UPDATE where a subquery has been neccessary. — Cenderze, Aug 16 '17 at 14:34

Zohar Peled · Accepted Answer · 2017-08-16T14:16:23.063

16

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)

edited Aug 16 '17 at 14:16

answered Aug 16 '17 at 14:10

Zohar Peled

79,642
10
69
121

This is something I have never seen before. Thanks! – Steven Lemmens Aug 16 '17 at 14:33
[Glad to help :-)](http://meta.stackoverflow.com/questions/291325/how-to-show-appreciation-to-a-user-on-stackoverflow/291327#291327) – Zohar Peled Aug 16 '17 at 14:34
1

I marked you as the answer and upvoted it to show appreciation :-) – Steven Lemmens Aug 16 '17 at 14:42

score 1 · Answer 2 · answered Aug 16 '17 at 14:10

1

Try this:

UPDATE s
   SET Value = x.cnt
from statisticsTable s
 outer apply (select count(*) as cnt
                FROM OtherTable o
                WHERE o.UserId = s.UserId ) x
WHERE s.id in (1,2,3)

answered Aug 16 '17 at 14:10

Mat

213
1
11

score 0 · Answer 3 · answered Nov 21 '22 at 17:45

An alias for the table you are updating is not allowed but you can use the table name in the normal way:

UPDATE statisticsTable
SET Value = (select count(*) 
            FROM OtherTable o
            WHERE o.UserId = statisticsTable.UserId ) 
WHERE id in (1,2,3)

SQL Server: how to use alias in update statement?

3 Answers3