SQL Server：内部联接中使用的表变量

What is the problem with following SQL. Can table variable not be used in JOIN clause?

以下SQL有什么问题。表变量是否可以在JOIN子句中使用？

Error msg is of "Msg 170, Level 15, State 1, Line 8 Line 8: Incorrect syntax near 't1'."

错误消息是“消息170，级别15，状态1，行8行8：'t1'附近的语法不正确。”

Declare @t TABLE (
    _SportName  varchar(50),
    _Lang       varchar(3)
)

insert @t VALUES('Basketball', 'ENG') -- ENG

UPDATE tblSport t1 
SET 
    t1.SportName = @t._SportName
FROM 
    @t INNER JOIN tblSport ON (t1.Lang = @t._Lang)

Thanks.

谢谢。

5 个解决方案

#1

Change your last statement to:

将您的上一个陈述更改为：

UPDATE t1, temp
SET t1.SportName = temp._SportName
FROM tblSport AS t1
INNER JOIN @t AS temp
    ON t1.Lang = temp._Lang

(need to check exact syntax)

（需要检查确切的语法）

#2

Apart from the t1 alias being in the wrong place, nobody else mentioned using square brackets around the table variable, instead of an alias. Changing the update statement to the following will work too:

除了t1别名在错误的地方之外，没有人提到在表变量周围使用方括号而不是别名。将update语句更改为以下内容也可以：

UPDATE t1
SET
    t1.SportName = [@t]._SportName
FROM
    @t INNER JOIN tblSport t1 ON t1.Lang = [@t]._Lang

_{[Tested on SQL Server 2005.]}

[在SQL Server 2005上测试过。]

#3

Justin's answer is correct syntactically - you need to assign an alias to the temp table (same for table type variables in 2008).

Justin的答案在语法上是正确的 - 您需要为临时表分配一个别名（2008年的表类型变量也是如此）。

However, be aware that neither table variables nor table-type variables have any statistics associated with them, and therefore can lead the query optimiser to make very dubious choices with regard to execution plans (because it will always estimate that the table variable contains 1 row - and therefore usually chooses nested loops as a join operator).

但是，请注意，表变量和表类型变量都没有与之关联的任何统计信息，因此可能导致查询优化器对执行计划做出非常可疑的选择（因为它总是会估计表变量包含1行 - 因此通常选择嵌套循环作为连接运算符）。

#4

Your alias t1 is in the wrong place

你的别名t1在错误的地方

UPDATE
    t1 
SET 
    SportName = @t._SportName
FROM 
    @t INNER JOIN tblSport t1 ON (t1.Lang = @t._Lang)

#5

don't forget use alias for variable tables

不要忘记使用变量表的别名

Declare @t TABLE (
    _SportName  varchar(50),
    _Lang       varchar(3)
)

insert @t VALUES('Basketball', 'ENG') -- ENG

UPDATE t1 
SET 
    t1.SportName = t2._SportName
FROM tblSport t1 INNER JOIN
    @t as t2  ON (t1.Lang = t2._Lang)

#1