SQL Server: table variable used in a inner join

Question

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

Error msg is of "Msg 170, Level 15, State 1, Line 8 Line 8: Incorrect syntax near '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.

Answer 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)

Answer 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:

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

_{[Tested on SQL Server 2005.]}