from @outcome o, @tmp5 t5
where o.column2 = @tmp3.column2
and t5.column3= o.column3
and o.column4is null
But, I can't get this procedure to compile, since the compiler gives me the following message: 'Must declare the variable @tmp3', referencing the line in my where-clause of my subquery.
I asume that I have reached a limitation of table variables, and I could really need a few thoughts regarding any workaround of this. Is it possible to rewrite my updatestatement in any way that make this update run?
The select works just fine. I can't add table 3 to the from list, since the result of the update will be wrong. @tmp3 in the where-clause is used as a reference to the outer update. Without this reference, the update will set the same value in column1 for all rows. And that is not as it is suppose to be.
Originally posted by rohitkumar
is your select query working fine if run seperately?
add table 3 in the "from" tables list.
This, for example, is better syntax using subquery:
set column1 = SubTotals.Total
from @tmp3 tmp3
(select o.column2, count(o.column1) Total
from @outcome o
inner join @tmp5 t5 on o.column3 = t5.column3
where o.column4is null
group by o.column2) SubTotals
on tmp3.column2 = SubTotals.column2