July 9, 2008 at 12:42 pm
I have the following update statement :
Update consultant.consultant
SET SponsorXID = a.XID
FROM[consultant].[Consultant] AS a
INNER JOIN [consultant].[Consultant] AS b ON
a.[1stSponsorID] = b.consultantid
where a.consultantID = '0000087'
But it keeps returning the following errors:
Msg 8154, Level 16, State 1, Line 1
The table 'consultant.consultant' is ambiguous.
Any ideas?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
July 9, 2008 at 12:51 pm
Did you try to change
'consultant.consultant'
to b.consultant
?
July 9, 2008 at 12:54 pm
Not sure I follow?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
July 9, 2008 at 1:33 pm
You either want
[font="Courier New"]Update a
SET a.SponsorXID = a.XID
FROM [consultant].[Consultant] AS a
INNER JOIN [consultant].[Consultant] AS b ON
a.[1stSponsorID] = b.consultantid
where a.consultantID = '0000087'[/font]
or
[font="Courier New"]Update b
SET b.SponsorXID = a.XID
FROM [consultant].[Consultant] AS a
INNER JOIN [consultant].[Consultant] AS b ON
a.[1stSponsorID] = b.consultantid
where a.consultantID = '0000087'[/font]
however, I have the same problem SQL Server has - your query is ambiguous so I don't know which side of your join you do want to update. It is one of the two though.
July 9, 2008 at 1:45 pm
I have tried both version and they work kinda of.
Update b
SET b.SponsorXID = a.XID
FROM [consultant].[Consultant] AS a
INNER JOIN [consultant].[Consultant] AS b ON
a.[1stSponsorID] = b.consultantid
where a.consultantID = '0000087'
The issue is now that the SponserXID is getting update to the same XID as the consultant instead on the sponsor.
Example;
ConsultantID = 10001
XID = 100
1stSponsorID = 2000
SponsorXID = 100 (Instead of the appropriate XID for that consultant).
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
July 9, 2008 at 2:06 pm
The UPDATE part looks good. You are attempting to modify the b table value so I'd go with B.Consultant in the UPDATE clause. Are you sure that your JOIN conditions are correct?
It may help if you could post some sample rows that show the update conditions that you are seeing.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply