Need help with an UPDATE query

mainrotor used Ask the Experts™
Hey Experts,
I am trying to update a value on a field in a SQL SERVER table from a function that exists in a Linked Server.

I have tried using the syntax below, but I get this error:  Incorrect syntax near the keyword 'SELECT'.

UPDATE [dbo].[ORDER] set [OrderDescription] = SELECT * FROM OPENQUERY([SERVER2], 'Select [Database2].[dbo].[GetMarket]('ABC')

What do I have to do to fix this?

Thanks in advance,
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Fixed the syntax issue, but please be noted that there are no JOIN condition or WHERE condition in your original query..
So, literally all records will be updated with a single value without any conditions..
Kindly verify whether it is intended or not.
set [OrderDescription] = (SELECT * FROM OPENQUERY([SERVER2], 'Select [Database2].[dbo].[GetMarket](''ABC'')'))

Open in new window

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Well, let's break it down a bit first....

Your openquery select - needs to return a value that can satisfy the update - not entirely happy with a select *. But lets move on and check components of your statement. Starting with
SELECT * FROM OPENQUERY([SERVER2], 'Select [Database2].[dbo].[GetMarket](''ABC'')')

Open in new window

Note a missing closing bracket....

Does that really achieve your goal ? Does it return a single usable value (despite the select *) ?

If so, then next step....

Do you really want to update every instance of OrderDescription to the same value ?

If not, then need to have extra dependencies in there to narrow down the affected rows.... Or maybe the function "knows" a relationship which is obfuscated by using a generic 'ABC' as a parameter.

Given consideration of the above, then your syntax should become
UPDATE [dbo].[ORDER] set [OrderDescription] = (SELECT * FROM OPENQUERY([SERVER2], 'Select [Database2].[dbo].[GetMarket]('ABC'))

Open in new window

But still not happy with the generic select *

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial