DELETE query against a linked server issues error

Question

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?

• Vassilis: I forgot to make evident that the field name is the same in both tables. Just a heads-up..
• Vassilis: Well, it seems that at the second WHERE clause and we just need to reference the table name and not the whole multipart identifier as in the FROM clause. I find this a bit weird why would the engine behave like that but something is escaping me evidently. Thanks to Hugo Cornellis for answering at the #sqlhelp ;)

Answer 1

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...