Problem with UPDATE on table variable using subquery

[Fhwqhgads]

It appears that others have had this same problem, but I have not managed to find the solution.

Server 2005 gives a "must declare scalar variable @TIDTable" error on the following Update command:

Update @TidTable
Set Connection =1
From (Select * From @tidtable a
Where Connection !=2 
and Trank2 in (Select top 3 Trank2 from @tidtable b where a.Trank1=b.Trank1 Order by radius asc)) as c
Where @tidtable.Trank1=c.Trank1

I have declared @TidTable as a table variable and this is the only error it shows [the error being in the WHERE clause.]

If I were allowed to alias @tidtable by saying "Update @tidtable as d" I think it would work. However, that isn't allowed.

I tried simplifying the code to make sure this was the problem [and not something else]. The same error occurs with the following:

Update @tidtable
Set Connection = 1
From (Select Top 10 * from @tidtable Order by Trank1) a
Where @tidtable.Trank1=a.Trank1

 

[onpnt]

Try forum183

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]