July 15, 2012 at 1:55 am
update TblGas
set MedianCo=
(SELECT MedianCo
FROM TblGas INNER JOIN
Total ON TblGas.ID_NO= Total.ID_No
)
The above statement gives the following error message
Ambiguous Column name'MedianCo'
please help me
July 15, 2012 at 3:00 am
Qualify your column names. I'm going to guess that the MedianCo in the subquery should be from the Total table?
update TblGas
set TblGas.MedianCo=
(SELECT t.MedianCo
FROM TblGas AS tg INNER JOIN
Total AS t ON tg.ID_NO= t.ID_No
)
If so, this is an easier way to write that.
update TblGas
set TblGas.MedianCo= t.MedianCo
FROM TblGas INNER JOIN
Total AS t ON TblGas.ID_NO= t.ID_No
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2012 at 3:36 am
Hi Gail,
I wonder if there is a difference between your update query and this:
update TblGas
set TblGas.MedianCo= t.MedianCo
FROM Total AS t
where TblGas.ID_NO= t.ID_No
when I compeer the Execution Plans they look the same.
July 15, 2012 at 3:54 am
There have been reported cases where not having the updated table in the from clause has resulted in really odd, inefficient execution plans.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2012 at 4:41 am
Thanks Gail,
I knew that there must be some reason for the longer version.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply