CODEWITHSUNDEEP
sql-serversubqueryaliaslinked-server
imbrian21
imbrian21

Reputation: 121

SQL - Update command with subquery and linked server

I'm currently attempting to run an update on a table using a linked server

Ex.

update table1 a
set a.column1=(select Count(b.column1) FROM linkedserver.databse.table b)
where a.column2=b.column2
and a.column3=b.column3

My problem is:

The multi-part identifier "linkedserver.databse.table.column" could not be bound.

The only way i can see this working is with aliases and i know the server has the appropriate access to the linked server.

Upvotes: 0

Views: 335

Answers (3)

bjnr
bjnr

Reputation: 3437

Use UPDATE FROM statement and 4 parts table name when you're working with linked servers. 

update table1
set column1=(select Count(b.column1) FROM linkedserver.databse.dbo.table b)
from table1 a
where a.column2=b.column2 and a.column3=b.column3

Upvotes: 0

Joe
Joe

Reputation: 6827

Not certain it's the cause of your problem, since I would expect to see a different error, but your syntax should be:

update a
   set a.column1=(select count(b.column1) 
                    from linkedserver.databse.table b
 where a.column2=b.column2
       and a.column3=b.column3)
  from table1 a

Upvotes: 0

gh9
gh9

Reputation: 10703

You need to include the schema in your query

try using 

LinkedServer.Database.Schema.Table

Or

LinkedServer.Database..Table   (if schema is dbo)

Upvotes: 1

Related Questions