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

 

[cLFlaVA]

Fhwqhgads!!!!!!!!!

THAT IS THE BEST NAME EVER!!!!

come, on, Fhwqhgads, i said come-on-Fhwqhgads!



*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[AlexCuse]

I cannot figure out the logic to change your initial query into a working one, but it is possible. Consider this test:

declare @test table
(
id int identity (1,1),
someval varchar(13),
bestname varchar(5)
)

insert into @test (someval)
select 'Fhwqhgads'
union all select 'clFlava'
union all select 'AlexCuse'

update @test
set bestname = 'True'
where id in (select top 1 ID from @test)

select * from @test

Building on this test, if your simplified version of the query is written properly, it will look like this (note I have NOT tested this, I just know that it will parse correctly):

Update @tidtable
Set Connection = 1
From @tidtable 
WHERE Trank1 in (Select Top 10 Trank1 from @tidtable Order by Trank1)

The problem with your query is that you seem to be using JOIN and WHERE and FROM components interchangeably.

I hope that this will help you structure the more complicated query properly. Please post back with what you come up with.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Fhwqhgads]

In Buck Woody's extensive tutorial on SQL, he gives the following code for an update to a table, showing how to use subqueries in an Update statement:

UPDATE authors
SET state = 'ZZ' 
FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1
WHERE authors.au_id = t1.au_id

I don't see how his example is different from mine structurally, except that I am using a table variable instead of a table.

 

[bikerboy718]

Where your problem is for this update is when you specify the from. The update query is looking to update the from table in statement. More has to be done in the where clause and less in the from clause.


With Great Power Comes Great Responsibility!!!

Michael

 

[AlexCuse]

The query you cite can be rewritten like so:

update authors
set state = 'ZZ'
WHERE au_id in (select top 10 au_id from authors order by au_lname)

So building on this, you should be able to fix your query. If not, please post sample data and desired results, as your query does not make it immediately clear what you are trying to do.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Hey Fhwqhgads -

I think this is what you need to do to get your query to work:

Update a
Set Connection =1
From 
@tidtable a
inner join
(
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
on a.Trank1=c.Trank1

Notcie that what you were doing in your where clause is now accomplished using a subquery that is joined to the table for updating (@tidtable). Because you don't want to update your subquery, you want to update the original table. Does this make sense? If it does not, don't hesitate to ask questions because this is good stuff to understand.

Anyway, let me know if that query works.

hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Fhwqhgads]

I got the output I was looking for by using the following:

Update @TidTable
Set Connection =1
Where Connection !=2 and Exists
(Select * From
(Select Trank1 as home,Trank2 as away
From @tidtable a
Where Trank2 in (Select Top 3 Trank2 From @tidtable where Trank1=a.Trank1)) c
Where c.home=Trank1 and c.away=Trank2)

Note, I maintain that the problem is not in the update statement itself. To prove this I simply created a "real" table called tidtable. The following code worked just fine:

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

Whereas the following code throws an error

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

I think the problem may be a scope issue with table variables.