Ambiguous column name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Ambiguous column name

Author

Topic

rh_hosseiny
Starting Member

5 Posts

Posted - 2011-05-04 : 05:00:21

Hi all,
i have Ambiguous column name 'IdTypeTakhasos' in bellow query

CREATE PROCEDURE dbo.Doctor_Update
(
@IdDoctor int,
@Fname nvarchar(20),
@Lname nvarchar(20),
@ShNezamPezeshki varchar(10),
@IdTypeTakhasos int,
@UpdateDate nvarchar(10)
)
as
update Tbl_Doctor set Fname=@Fname,Lname=@Lname,ShNezamPezeshki=@ShNezamPezeshki,
IdTypeTakhasos=(select IdTypeTakhasos from Tbl_TypeTakhasos t inner join Tbl_Doctor D on t.IdTypeTakhasos=D.IdTypeTakhasos),
UpdateDate=@UpdateDate
where IdDoctor=@IdDoctor
could anyboby help me?
thank u in advance

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-04 : 05:10:08

select t.IdTypeTakhasos from Tbl_TypeTakhasos t inner join Tbl_Doctor D on t.IdTypeTakhasos=D.IdTypeTakhasos
But this doesn't make sense
I suspect you want something more like this

update Tbl_Doctor
set Fname=@Fname,Lname=@Lname,ShNezamPezeshki=@ShNezamPezeshki,
IdTypeTakhasos=(select t.IdTypeTakhasos from Tbl_TypeTakhasos t where t.IdTypeTakhasos=D.IdTypeTakhasos),
UpdateDate=@UpdateDate
from Tbl_Doctor D
where IdDoctor=@IdDoctor

but this still won't be right
maybe - I suspect you want some other relation to get the value.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-04 : 07:08:08

or you can use
UPDATE Tbl_Doctor
SET Fname = @Fname,
Lname = @Lname,
ShNezamPezeshki = @ShNezamPezeshki,
UpdateDate = @UpdateDate,
IdTypeTakhasos = D.IdTypeTakhasos
FROM Tbl_Doctor T
INNER JOIN Tbl_TypeTakhasos D ON T.IdTypeTakhasos = D.IdTypeTakhasos
WHERE IdDoctor = @IdDoctor

But if IdTypeTakhasos is same on both side then WHY we are joining ;) and using in UPDATE

--------------------------
http://connectsql.blogspot.com/

wided
Posting Yak Master

218 Posts

Posted - 2011-05-04 : 07:13:43

try this

update Tbl_Doctor set Fname=@Fname,Lname=@Lname,ShNezamPezeshki=@ShNezamPezeshki,
UpdateDate=@UpdateDate,
Tbl_Doctor.IdTypeTakhasos= Tbl_TypeTakhasos.IdTypeTakhasos
from Tbl_TypeTakhasos
where Tbl_TypeTakhasos.IdTypeTakhasos=Tbl_Doctor.IdTypeTakhasos
and Tbl_Doctor.IdDoctor=@IdDoctor

wided
Posting Yak Master

218 Posts

Posted - 2011-05-04 : 07:23:02

i think you have not to update Tbl_Doctor.IdTypeTakhasos
is is the same in the to tables

update Tbl_Doctor set Fname=@Fname,Lname=@Lname,ShNezamPezeshki=@ShNezamPezeshki,
UpdateDate=@UpdateDate
where Tbl_Doctor.IdDoctor=@IdDoctor

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-04 : 07:25:24

quote:
Originally posted by wided

try this

update Tbl_Doctor set Fname=@Fname,Lname=@Lname,ShNezamPezeshki=@ShNezamPezeshki,
UpdateDate=@UpdateDate,
Tbl_Doctor.IdTypeTakhasos= Tbl_TypeTakhasos.IdTypeTakhasos
from Tbl_TypeTakhasos
where Tbl_TypeTakhasos.IdTypeTakhasos=Tbl_Doctor.IdTypeTakhasos
and Tbl_Doctor.IdDoctor=@IdDoctor

see my comment
that won't change the value of IdTypeTakhasos as it's the join column.
All it will do is change the updateddate.
(unless I'm missing a column name difference)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources