Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.


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?

share|improve this question
    
I forgot to make evident that the field name is the same in both tables. Just a heads-up.. –  bull Sep 3 '13 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 ;) –  bull Sep 3 '13 at 13:08

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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