Reputation: 23
I need your help. I have a command to update rows, this works if in a variable @ID contain one value.
DECLARE @ID nvarchar(100)
SET @ID = (select top 1 id from [SERVICES])
DECLARE @UPDATE nvarchar(max)
SET @UPDATE ='UPDATE SERVICES
SET SERVICES.options = t1.options
FROM SERVICES t
JOIN (SELECT *
FROM OPENQUERY([ORI], ''SELECT ID, options
FROM log
WHERE ID = ''''' + @ID + ''''' '')) t1 ON t1.id = t.id'
EXEC (@UPDATE)
but I need to update more than 1 rows.
If I specify a condition like this:
SET @ID = (SELECT id FROM [ReportSM].[dbo].[SERVICES])
I get an error:
Subquery returned more than 1 value.
How to fix it?
Upvotes: 0
Views: 637
Reputation: 400
Option 1
In your current setup, you could pass @ID as a CSV to OPENQUERY such that WHERE IN would work.
WHERE ID = ' + @ID + '
could then be replaced with
WHERE ID IN (' + @IDs + ')'
Look here to convert your ID column into a CSV: SQL Server convert select a column and convert it to a string
Be aware of the limit on the length of the IN clause. See this https://dba.stackexchange.com/questions/14161/what-is-the-maximum-number-of-parameters-i-can-pass-using-the-sql-in-clause-in-s
Option 2
Since concatenating data directly into a query has SQL injection concerns, you could also look at a more structured approach of using FOR XML to convert the IDs into an xml fragment and passing that into OPENQUERY and within that reading the ids out using OPENXML.
If both your servers are SQL Server 2016 or above, you could also use JSON as your format for transferring the ids instead of XML. You would use FOR JSON to create a JSON array containing the ids and use OPENJSON on the destination SQL server to convert the JSON back into a rowset that you can join.
declare @json varchar(max) = (select id from [ReportSM].[dbo].[SERVICES] FOR JSON PATH)
This will generate a string like this
[{"id":1},{"id":2},{"id":3},{"id":4}]
You can add this into a variable in the query you are preparing and read it using below
SELECT ID
FROM OPENJSON (@json, '$')
WITH (ID IN '$.id')
Putting it together your query would look like this:
declare @json varchar(max) = (select id from [ReportSM].[dbo].[SERVICES] FOR JSON PATH)
DECLARE @UPDATE nvarchar(max)
SET @UPDATE ='UPDATE SERVICES
SET SERVICES.options = t1.options
FROM SERVICES t
JOIN (SELECT *
FROM OPENQUERY([ORI], ''DECLARE @json nvarchar(max) = ''''' + @json + '''''
SELECT ID, options
FROM log
WHERE ID IN (SELECT ID FROM OPENJSON (@json, ''$'') WITH (ID IN ''$.id'')))) t1 ON t1.id = t.id'
EXEC (@UPDATE)
Upvotes: 1
Reputation: 1160
It sounds like you really want to pass a table valued parameter into the open query, but that's not supported. You can remove that filter and let the join take care of the update accuracy but that will result in a potentially much more expensive remote query than necessary. That solution would just look like this:
UPDATE
t
SET
t.options = t1.options
FROM
Services t
JOIN (SELECT ID, options FROM OPENQUERY([ORI], 'SELECT ID, options FROM
log')) t1 ON t1.id = t.id
However, if you have control over the ORI
linked server, you could set up a linked server there back to your ReportSM
server. That would let you create a view on your ORI
server that contains all of the IDs from your [ReportSM].[dbo].[SERVICES]
table which is what you are trying to filter your log
table on. That means you could perform the ID filtering on the ORI
side and then run a simpler update on the ReportSM
side. Something like this on the ORI
side:
CREATE VIEW vReportServiceIDs
AS
SELECT
ID
FROM
[ReportSM].[dbo].[SERVICES]
CREATE VIEW vReportServiceLogs
AS
SELECT
reportService.ID,
oriLog.options
FROM
vReportServiceIDs reportService
JOIN [log] oriLog ON reportService.ID = [log].ID
And then on your ReportSM
side:
UPDATE
t
SET
t.options = t1.options
FROM
SERVICES t
JOIN (
SELECT
ID, options
FROM
OPENQUERY([ORI], 'SELECT ID, options FROM vReportServiceLogs')
If you do not have that kind of access to the ORI
server and the logs
table has too much data for you to just query it all and exclude what you don't need during the join, you might want to consider creating a cache of the logs
table that you update from a job on the ReportSM
server and then just joining on that during your update.
Upvotes: 1