I'm trying to use the following code to execute a DELETE statement on a linked server. I'm trying to delete all records from the remote server where there is a match on a certain ID field in both the table on the linked server and a local table. Any records showing up in the INNER JOIN should be deleted from the linked server table RemoteTable.

The following isn't allowing me to use an alias ("RemoteTable") for my remote table. Am I on the right track here with what I'm trying to accomplish? Thanks!

DELETE FROM OPENQUERY(LINKEDSERVER_MYSQL,'SELECT * from PrimaryInquiry_T2') AS RemoteTable INNER JOIN Forms.dbo.PrimaryInquiry_TLOCAL AS LocalTable ON LocalTable.PrimaryInquiry_TLOCAL_EntryID = RemoteTable.PrimaryInquiry_T2_EntryID

  • Did you try DELETE RemoteTable FROM OPENQUERY(...) AS RemoteTable ...? – Aaron Bertrand Dec 17 '14 at 20:29
  • @AaronBertrand That did it! Thanks so much! So I'm assuming this is the best way to accomplish a DELETE on a linked server with a local table as criteria? – mntyguy Dec 17 '14 at 20:42
  • Sure, there are other forms (like EXISTS) but this should do just fine. – Aaron Bertrand Dec 17 '14 at 20:50
  • @AaronBertrand Awesome. I was just concerned with making sure I'm using the most efficient method but sounds like it's pretty much 6 to one half dozen to the other in this case. Thanks again! – mntyguy Dec 17 '14 at 21:05

You should be able to use SQL Server's proprietary and admittedly awkward self-reference form of delete:

DELETE RemoteTable
  FROM OPENQUERY(...) AS RemoteTable ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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