Error Message 'The table is ambiguous' 2

[stanja]

I try to update a table on a linked server but i receive the following error The table 'shop500.shopping500tp.thysinterieur.categories' is ambiguous.

This is the instruction

update [shop500].shopping500tp.thysinterieur.categories
set highercategoryid=C2.categoryid
from [shop500].shopping500tp.thysinterieur.categories tshop, [shop500].shopping500tp.thysinterieur.categories C2
where left(tshop.CATEXTRA,len(tshop.CATEXTRA)-1)=C2.CATEXTRA
and len(tshop.CATEXTRA)>2

There are no triggers
How can i solve this problem ?

TX
Stanja

 

[dan1967]

Try fully qualifying highercategoryid with the servername.owner.databasename.highercategoryid.

Dan.

 

[SQLBill]

You are using two DIFFERENT categories tables. One is from tshop and the other is from C2. But you don't say which to use in the first line:

update [shop500].shopping500tp.thysinterieur.categories

try this and test it:

update tshop.categories

-SQLBill

 

[stanja]

Dan1967,

if i do the thing you proposed i receive the next error

Server: Msg 117, Level 15, State 1, Line 2
The object name 'shop500.shopping500tp.thysinterieur.categories.' contains more than the maximum number of prefixes. The maximum is 3.


update [shop500].shopping500tp.thysinterieur.categories.categories
set highercategoryid=C2.categoryid
from [shop500].shopping500tp.thysinterieur.categories tshop, [shop500].shopping500tp.thysinterieur.categories C2
where left([shop500].shopping500tp.thysinterieur.categories.CATEXTRA,len([shop500].shopping500tp.thysinterieur.categories.CATEXTRA)-1)=C2.CATEXTRA
and len([shop500].shopping500tp.thysinterieur.categories.CATEXTRA)>2

other suggestions ?

 

[tektipdjango]

try this

update tshop
set highercategoryid=C2.categoryid
from [shop500].shopping500tp.thysinterieur.categories tshop, [shop500].shopping500tp.thysinterieur.categories C2
where left(tshop.CATEXTRA,len(tshop.CATEXTRA)-1)=C2.CATEXTRA
and len(tshop.CATEXTRA)>2


django
bug exterminator
tips'n tricks addict