Sql-server – DELETE query against a linked server issues error

deletelinked-serversql serversql-server-2008

I have a query that is part of a bigger process, and runs against a linked server.

The code is like this:

DELETE FROM [LinkServerIP].[Database].[dbo].[TableName]
WHERE NOT EXISTS (
    SELECT * 
    FROM [LinkServerIP].[Database].[dbo].[AnotherTableName] t 
    WHERE t.[Code]=[LinkServerIP].[Database].[dbo].[TableName].[Code]
);

Now, when I run this I get:

Msg 4104, Level 16, State 1, Procedure xxxx, Line 113 The multi-part identifier "LinkServerIP.Database.dbo.TableName.Code" could not be bound.

Any ideas?

Best Answer

Just use the syntax that allows you to define an alias:

DELETE t1 FROM Server.Database.dbo.Table1 AS t1
WHERE NOT EXISTS 
(
  SELECT 1 FROM Server.Database.dbo.Table2 AS t2
  WHERE t2.Code = t1.Code
);

Or you can use dynamic SQL to simplify:

DECLARE @sql NVARCHAR(MAX) = N'DELETE t1 FROM dbo.Table1 AS t1
  WHERE NOT EXISTS
  (
    SELECT 1 FROM dbo.Table2 AS t2
    WHERE t2.Code = t1.Code
  );';

EXEC Server.Database..sp_executesql @sql;

This forces the query to be run on the other side, which can provide other benefits too...

Related Question