Need help with an UPDATE query

mainrotor
mainrotor used Ask the Experts™
on
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

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
Commented:
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.
UPDATE [dbo].[ORDER]
set [OrderDescription] = (SELECT * FROM OPENQUERY([SERVER2], 'Select [Database2].[dbo].[GetMarket](''ABC'')'))

Open in new window

Mark WillsTopic Advisor, Page Editor
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 *

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