SQL - Update command with subquery and linked server

Question

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.

Answer 1

You need to include the schema in your query

try using

LinkedServer.Database.Schema.Table

Or

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

Answer 2

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

Answer 3

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