t-sql使用内部连接更新多个行

i trying to update a column in my table for several rows at the same time.

我试图同时为几行更新表中的一列。

i want get the value to update from an another table which has a foreignkey.

我想从另一个具有foreignkey的表中获取要更新的值。

Table Table1:

Id Primary
UserNumber INT


Table Table2
Id Primary
Id_T1 ForeignKey
UserId INT
OrderNumber INT

can someone help with this pls?

有人能帮忙吗?

Note: In the Table T2 i need the UserId Value Where the ordernumber has the maximum value.

注意:在表T2中，我需要UserId值，其中ordernumber具有最大值。

for example:

例如:

Id_T1 UserId OrderNumber 
15    24        1 
15    55        2 
15    72        3

the value i want to receive is the 72.

我想要的值是72。

i have try this:
update T1 set T1.UserNumber = T2.UserId
FROM Table1 AS T1
INNER JOIN Table2 AS T2
ON T1.Id = T2.IdMain
WHERE T1.Id = T2.IdMain
AND T2.OrderNumber = (SELECT MAX(T2.OrderNumber) FROM Table2)

but i get this error:

但是我得到了这个错误:

Msg 147, Level 15, State 1, Line 6 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

msg147，第15级，状态1，第6行，一个聚合可能不会出现在WHERE子句中，除非它位于包含在have子句或select列表中的子查询中，并且被聚合的列是一个外部引用。

1 个解决方案

#1

You don't need to use alias name in inner query. Try this:

在内部查询中不需要使用别名。试试这个:

UPDATE T1 
SET T1.UserNumber = T2.UserId
FROM Table1 AS T1 INNER JOIN 
     Table2 AS T2 ON T1.Id = T2.IdMain
WHERE T1.Id = T2.IdMain
     AND T2.OrderNumber = (SELECT MAX(OrderNumber) FROM Table2)