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
– justiceorjustus, Commented Aug 16, 2017 at 14:10
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
– Cenderze, Commented Aug 16, 2017 at 14:34

Zohar Peled · Accepted Answer · 2017-08-16 14:16:23Z

17

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, 2017 at 14:16

answered Aug 16, 2017 at 14:10

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Steven Lemmens Over a year ago

This is something I have never seen before. Thanks!

Zohar Peled Over a year ago

Glad to help :-)

Steven Lemmens Over a year ago

I marked you as the answer and upvoted it to show appreciation :-)

Mat · Accepted Answer · 2017-08-16 14:10:35Z

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, 2017 at 14:10

Mat

3232 silver badges12 bronze badges

Comments

Stephen Westlake · Accepted Answer · 2022-11-21 17:45:42Z

1

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)

answered Nov 21, 2022 at 17:45

Stephen Westlake

412 bronze badges

Comments

Tulip · Accepted Answer · 2023-10-28 21:48:22Z

0

The second "UserId" field comes from statisticsTable rather from OtherTable because the SQL server will first look for the "UserId" column within the statisticsTable. If "UserId" column exists in the statisticsTable, it will be used in the condition o.UserId = UserId. However, if there's no "UserId" column in the statisticsTable, SQL Server will then look for it in the OtherTable.

You can write the query using an alias to statisticsTable like 'stat'.

UPDATE stat
SET Value = (
SELECT COUNT(*) FROM OtherTable o
WHERE o.UserId = stat.UserId
)
FROM statisticsTable stat
WHERE stat.id IN (1,2,3);

answered Oct 28, 2023 at 21:48

Tulip

213 bronze badges

Collectives™ on Stack Overflow

SQL Server: how to use alias in update statement?

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related