We help IT Professionals succeed at work.

Need help with an UPDATE query

109 Views
Last Modified: 2018-09-17
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'.


QUERY SYNTAX:
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,
mrotor
Comment
Watch Question

SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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 *
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.