SQL Server Performance Forum – Threads Archive
error msg return Ambiguous column name
Hi,below is my sql statement that wil join and update the values, but it giving me error
Ambiguous column name both table hold the same column name, so what should I do to
get the sql statement running,
can’t change the column name coz it will effect many sp eror while running update i
set
i.packid=b.packid ,i.deckid =b.deckid
from individual i
inner join port b
on i.msn=b.msn
where i.empid=1120
I ran follwing scripts create table individual(packid int,deckid int ,empid int,msn varchar(20))
create table port(packid int,deckid int ,empid int,msn varchar(20)) update i
set
i.packid=b.packid ,i.deckid =b.deckid
from individual i
inner join port b
on i.msn=b.msn
where i.empid=1120 and insernt some data Then it gives me I ros updated message meaning that query is running perfectly 🙁
Try it without the i alias in the SET clause, like so: update i
set packid = b.packid, deckid = b.deckid
from individual i
inner join port b
on i.msn = b.msn
where i.empid = 1120
Might be worth a try. But if I’ve seen this correctly, it should also work with the alias. What is the exact error message? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
i GIVING TRY as below statement update i
set packid = b.packid, deckid = b.deckid
from individual i
inner join port b
on i.msn = b.msn
where i.empid = 1120 running OK… but when it excuted as stored procedure
an error msg pop us as below
Server: Msg 209, Level 16, State 1, Procedure Trial_insert_all, Line 288
Ambiguous column name ‘PackID’.
Server: Msg 209, Level 16, State 1, Procedure Trial_insert_all, Line 288
Ambiguous column name ‘DeckID’
quote:Originally posted by motioneye
i GIVING TRY as below statement
update i
set packid = b.packid, deckid = b.deckid
from individual i
inner join port b
on i.msn = b.msn
where i.empid = 1120 running OK… but when it excuted as stored procedure
an error msg pop us as below
Server: Msg 209, Level 16, State 1, Procedure Trial_insert_all, Line 288
Ambiguous column name ‘PackID’.
Server: Msg 209, Level 16, State 1, Procedure Trial_insert_all, Line 288
Ambiguous column name ‘DeckID’
set packid = b.packid, deckid = b.deckid
from individual i
inner join port b
on i.msn = b.msn
where i.empid = 1120 running OK… but when it excuted as stored procedure
an error msg pop us as below
Server: Msg 209, Level 16, State 1, Procedure Trial_insert_all, Line 288
Ambiguous column name ‘PackID’.
Server: Msg 209, Level 16, State 1, Procedure Trial_insert_all, Line 288
Ambiguous column name ‘DeckID’
it seems that you have post just a small portion of your sp ,because error message is for line no 288… may be it has more statements whick include the same columnname with same alias !
can you specify more !? Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami
Post the full query used in sp Madhivanan Failing to plan is Planning to fail
]]>