CODEWITHSUNDEEP
sqlsql-server
lolol
lolol

Reputation: 4390

referencing a table variable

I have a problem referencing a table variable in a update statement. Seems I can't use the @a.id column (the compiler says it's not declared).

The following example was written only to illustrate the problem, meaning that I know that I can solve the problem in the current example renaming the column id and avoiding the @a.id reference, but it's not a option, I really can't do that. I saw some solutions using the from statement to alias the table being updated, but in this example I'm using the from statement for something else. Is there another way to solve it?

declare @a table
(
    id int not null,
    name varchar(100) null
)

insert into @a (id, name) values (1, null)
insert into @a (id, name) values (2, null)
insert into @a (id, name) values (3, null)

declare @b table
(
    id int not null,
    name varchar(100) null
)

insert into @b (id, name) values (1, 'one')
insert into @b (id, name) values (2, 'two')

update @a
set
    name = f.name
from
(
    select
        id,
        name
    from @b
    where
        id = @a.id
) f
where
    @a.id = f.id

Upvotes: 1

Views: 3300

Answers (2)

Andrey Gurinov
Andrey Gurinov

Reputation: 2885

Try something like this:

declare @a table 
( 
    id int not null, 
    name varchar(100) null 
) 

insert into @a (id, name) values (1, null) 
insert into @a (id, name) values (2, null) 
insert into @a (id, name) values (3, null) 

declare @b table 
( 
    id int not null, 
    name varchar(100) null 
) 

insert into @b (id, name) values (1, 'one') 
insert into @b (id, name) values (2, 'two') 

update upd
set 
    name = [@b].name 
from @a AS upd
INNER JOIN @b
    ON upd.id = [@b].id

And BTW following will work too:

update @a 
set 
    name = f.name 
from 
( 
    select 
        id, 
        name 
    from @b 
) f 
where 
    [@a].id = f.id

Upvotes: 1

David W
David W

Reputation: 10184

The reference to @a.id is illegal because the table is out of scope at that point. Here's what you might try:

Update @a
  set name = f.name
  from @b g
  join @a f
    on g.id=f.id

Upvotes: 0

Related Questions