SQL: The table '#tbl_result' is ambiguous.

 
If you are SQL programmer, there may be cases where you want to join same table multiple times to pull out same data.
This is ok for select operation. But in case of update and delete operation, it may not be easy. The error
"The table 'tablename' is ambiguos' comes while performing such update operation.
The error can be overcomed by simple trick of sql. You can use a sub-query instead of table name.
 
Here is an example.
 
First a test table is created.
 
create table #tbl_result
(
 head varchar(20),
 item varchar(20),
 data numeric(18,3)
)
 
Some sample data is entered.
insert into #tbl_result values ( 'Volume', 'A', 10.2)
insert into #tbl_result values ( 'Volume', 'B', 11.2)
insert into #tbl_result values ( 'GTO', 'A', 1020)
insert into #tbl_result values ( 'GTO', 'B', 627.2)
insert into #tbl_result values ( 'Avg Price', 'A', 0)
insert into #tbl_result values ( 'Avg Price', 'B', 0)
 
--
Then following select statement works fine even though it contains same table multiple times

select *
from #tbl_result a
inner join #tbl_result b
 on a.item = b.item
 and a.head = 'Volume'
 and b.head = 'GTO'
 
But following update statement does not work and displays error message
-- Server: Msg 8154, Level 16, State 1, Line 1
-- The table '#tbl_result' is ambiguous.
 
update #tbl_result
set data = case when isnull(a.data,0) <> 0 then  b.data / a.data end
from #tbl_result a
inner join #tbl_result b
 on a.item  = b.item
 and b.head = 'GTO'
inner join #tbl_result c
 on a.item = c.item
 and c.head = 'Volume'
where a.head = 'Avg Price'
 
The update can be changed with a little trick to make it work. Here the table name is replaced with a sub query.

update #tbl_result
set data = case when isnull(c.data,0) <> 0 then  b.data / c.data end
from #tbl_result a
inner join ( select * from #tbl_result where head = 'GTO') b
 on a.item = b.item
inner join ( select * from #tbl_result where head = 'Volume') c
 on a.item = c.item
where a.head = 'Avg Price'
 
You can check out result by following select statement.
select *
from #tbl_result