update table variable with join

Question

Hi

I am using sql server 2000 and I want to update a table variable with join statement. Below is the code am using


update @test                
set col =
(
select max(anothercol) from realtable a (nolock)               
     where a.id=@test.id  
   ,b.name = @test.name
)
where col = 'x'

And I am getting an error something like 'must declare variable'. Please could anybody re-write and let me know as soon as possible.


Thanks,
Rakesh.

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

Answer 1

Try:

update T              
set col = 
(
select max(anothercol) from realtable a               
     where a.id=T.id  AND
    a.name = T.name
)
FROM @Test T
where col = 'x'

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog