Proper way to use table alias in linked server update query?

Question

I'm attempting to write an update query that uses both a local server and a linked server (amazon web service sql server). I need to use a table alias in order to truncate my statements to 4 denominations or less, such as [Server].[Database].[TableAlias].[Column] instead of [Server].[Database].[Owner].[Table].[Column] so that the multi-part identifier can be bound.

This is the update query I am attempting to use table aliases with:

UPDATE [Amazon IP].[AmazonDatabase].dbo.InkVials
SET InkRequestID = Local_Database.dbo.TasksInkRequests.InkRequestID
FROM Local_Database.dbo.TasksInkRequests
WHERE [Amazon IP].[AmazonDatabase].dbo.InkVials.InkRequestID = Local_Database.dbo.TasksInkRequests.JobRequestID
AND [Amazon IP].[AmazonDatabase].dbo.InkVials.Processing = 1;

I've tried multiple ways but don't seem to be doing it right.

Tab Alleman · Accepted Answer · 2014-08-21 17:50:36Z

1

This is normally how you use Table aliases to do an update with a joined table:

UPDATE i
SET InkRequestID = r.InkRequestID
FROM [Amazon IP].[AmazonDatabase].dbo.InkVials i
INNER JOIN Local_Database.dbo.TasksInkRequests r
  ON  i.InkRequestID = r.JobRequestID
WHERE i.Processing = 1;

edited Aug 21, 2014 at 17:50

answered Aug 21, 2014 at 16:49

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3633260 Over a year ago

this worked. I'm unsure exactly how it worked myself, but it worked great.

Collectives™ on Stack Overflow

Proper way to use table alias in linked server update query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related