Joings between two table variables

Is it possible to create a join between two table variables?
I tried doing something like this .. and it is throwing an error.
Declare @.tstTableOne table ( Sno int, FirstName varchar(100) )
Declare @.tstTableTwo table ( Sno int, LastName varchar(100) )
Insert @.tstTableOne Values(1, 'Dummy1')
Insert @.tstTableOne Values(2, 'Dummy2')
Insert @.tstTableTwo Values(1, 'Test1')
Insert @.tstTableTwo Values(2, 'Test2')
Update @.tstTableOne Set @.tstTable.FirstName = t.FirstName from @.tstTableTwo
t where t.Sno = @.tstTableOne.Sno
Regards
PradeepTry:
UPDATE t1
SET t1.FirstName = t2.LastName
FROM @.tstTableOne t1
INNER JOIN @.tstTableTwo t2 ON t1.Sno = t2.Sno
Let me know how you get on.
Damien
"SqlBeginner" wrote:

> Is it possible to create a join between two table variables?
> I tried doing something like this .. and it is throwing an error.
> Declare @.tstTableOne table ( Sno int, FirstName varchar(100) )
> Declare @.tstTableTwo table ( Sno int, LastName varchar(100) )
> Insert @.tstTableOne Values(1, 'Dummy1')
> Insert @.tstTableOne Values(2, 'Dummy2')
> Insert @.tstTableTwo Values(1, 'Test1')
> Insert @.tstTableTwo Values(2, 'Test2')
> Update @.tstTableOne Set @.tstTable.FirstName = t.FirstName from @.tstTableTw
o
> t where t.Sno = @.tstTableOne.Sno
> Regards
> Pradeep|||See if this helps
Declare @.tstTableOne table ( Sno int, FirstName varchar(100) )
Declare @.tstTableTwo table ( Sno int, LastName varchar(100) )
Insert @.tstTableOne Values(1, 'Dummy1')
Insert @.tstTableOne Values(2, 'Dummy2')
Insert @.tstTableTwo Values(1, 'Test1')
Insert @.tstTableTwo Values(2, 'Test2')
Update @.tstTableOne Set FirstName =LastName FROM
@.tstTableTwo t JOIN @.tstTableOne T1 ON t1.Sno =T.Sno
"SqlBeginner" <SqlBeginner@.discussions.microsoft.com> wrote in message
news:3ACD2367-C9D7-46F9-9938-88C278D6C9C0@.microsoft.com...
> Is it possible to create a join between two table variables?
> I tried doing something like this .. and it is throwing an error.
> Declare @.tstTableOne table ( Sno int, FirstName varchar(100) )
> Declare @.tstTableTwo table ( Sno int, LastName varchar(100) )
> Insert @.tstTableOne Values(1, 'Dummy1')
> Insert @.tstTableOne Values(2, 'Dummy2')
> Insert @.tstTableTwo Values(1, 'Test1')
> Insert @.tstTableTwo Values(2, 'Test2')
> Update @.tstTableOne Set @.tstTable.FirstName = t.FirstName from
> @.tstTableTwo
> t where t.Sno = @.tstTableOne.Sno
> Regards
> Pradeep|||Thanks it works. But any idea what is the bug in my Update statement?
Regards
Pradeep
"Damien" wrote:
> Try:
> UPDATE t1
> SET t1.FirstName = t2.LastName
> FROM @.tstTableOne t1
> INNER JOIN @.tstTableTwo t2 ON t1.Sno = t2.Sno
> Let me know how you get on.
> Damien
> "SqlBeginner" wrote:
>|||From BOL (UPDATE described):
"A table alias specified in a FROM clause cannot be used as a qualifier in
SET column_name"
You're mixing ANSI syntax with the T-SQL extension for UPDATEs and DELETEs
which allows you to specify multiple tables in a FROM clause. When using th
e
T-SQL syntax I _always_ specify the alias in the UPDATE clause and do not mi
x
aliased tables and non-aliased tables.
So,
UPDATE t
NOT UPDATE your_table
unless you are only updating one table, in which case "UPDATE your_table" is
correct.
Hope that helps!
Damien
"SqlBeginner" wrote:
> Thanks it works. But any idea what is the bug in my Update statement?
> Regards
> Pradeep
> "Damien" wrote:
>|||IMHO while using Joins in Table variables by definition or by rule we need t
o
use Alias names only. Since pradeep wasn't using that it throwed an error.
If at all he had tried this with Temp tables his logic would have worked as
it is without any hassles. Bcoz Temp tables doesn't have that rule.
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
"Damien" wrote:
> From BOL (UPDATE described):
> "A table alias specified in a FROM clause cannot be used as a qualifier in
> SET column_name"
> You're mixing ANSI syntax with the T-SQL extension for UPDATEs and DELETEs
> which allows you to specify multiple tables in a FROM clause. When using
the
> T-SQL syntax I _always_ specify the alias in the UPDATE clause and do not
mix
> aliased tables and non-aliased tables.
> So,
> UPDATE t
> NOT UPDATE your_table
> unless you are only updating one table, in which case "UPDATE your_table"
is
> correct.
> Hope that helps!
>
> Damien
> "SqlBeginner" wrote:
>|||This is what I meant,
Create table #tstTableOne table ( Sno int, FirstName varchar(100) )
Create table #tstTableTwo table ( Sno int, FirstName varchar(100) )
Insert #tstTableOne Values(1, 'Dummy1')
Insert #tstTableOne Values(2, 'Dummy2')
Insert #tstTableTwo Values(1, 'Test1')
Insert #tstTableTwo Values(2, 'Test2')
Update #tstTableOne Set #tstTableOne.FirstName = t.FirstName from
#tstTableTwo t where t.Sno = #tstTableOne.Sno
Select * from #tstTableOne
Select * from #tstTableTwo
Drop table #tstTableOne
Drop table #tstTableTwo
Best Regards
Vadivel
http://vadivel.blogspot.com
"Damien" wrote:
> From BOL (UPDATE described):
> "A table alias specified in a FROM clause cannot be used as a qualifier in
> SET column_name"
> You're mixing ANSI syntax with the T-SQL extension for UPDATEs and DELETEs
> which allows you to specify multiple tables in a FROM clause. When using
the
> T-SQL syntax I _always_ specify the alias in the UPDATE clause and do not
mix
> aliased tables and non-aliased tables.
> So,
> UPDATE t
> NOT UPDATE your_table
> unless you are only updating one table, in which case "UPDATE your_table"
is
> correct.
> Hope that helps!
>
> Damien
> "SqlBeginner" wrote:
>