1


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?

2
  • I forgot to make evident that the field name is the same in both tables. Just a heads-up..
    – Vassilis
    Sep 3, 2013 at 12:38
  • 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 ;)
    – Vassilis
    Sep 3, 2013 at 13:08

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.