Link to home
Start Free TrialLog in
Avatar of mainrotor

asked on

Need help with an UPDATE query

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,
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 *