CODEWITHSUNDEEP
sql-server
user3633260
user3633260

Reputation: 35

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

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.

Upvotes: 0

Views: 323

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

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;

Upvotes: 1

Related Questions