How to update multiple rows with primary key variable in SQL

Question

I have a table that is unique by primary key, and would like to update select rows by passing in an array.

However, I get the following error when trying to execute the stored procedure:

Incorrect syntax near '@indices'

Sample data:

key | someColumn
----+-----------
1   | data
2   | more data
3   | even more data

Query:

declare @indices nvarchar(max) = [1,2,3,...] -- this set by actually set by API

update thisTable 
set someColumn = 'data to update with',
where [i] in @indices;

Where indices = [1,2,3,...].

How can I pass in an array of keys to check against to update the table?

I would do this update with a join. – briskovich Jul 25 '19 at 17:54 — briskovich, Jul 25 '19 at 17:54
What would a join look like in this case? – Matthew Jul 25 '19 at 18:36 — Matthew, Jul 25 '19 at 18:36
My apologies, I misunderstood what you are trying to do. – briskovich Jul 25 '19 at 21:28 — briskovich, Jul 25 '19 at 21:28

score 2 · Accepted Answer · edited Jul 26 '19 at 01:21

2

If you are using SQL Server 2016 or later, you can use the STRING_SPLIT function in the following manner:

UPDATE thisTable
SET    someColumn = 'Some Value'
WHERE  [i] IN (SELECT * FROM STRING_SPLIT(@indices, ','));

If you are using earlier versions, you will need to resort to some uglier solutions, you can find more details here:

HTH

-- For SQL Server 2016
ALTER DATABASE [database_name_here]
SET COMPATIBILITY_LEVEL = 130 
GO

edited Jul 26 '19 at 01:21

Matthew

1,461
3
23
49

answered Jul 25 '19 at 18:08

SQLRaptor

671
4
14

Excellent, but he will also need to fix the declare to replace the `[ ]` with `' '` : `declare @indices nvarchar(max) = '1,2,3,...'` – daShier Jul 25 '19 at 18:11
Getting an errror `Invalid object name 'string_split'` with SQL Server version `Microsoft SQL Server 2017`. – Matthew Jul 25 '19 at 18:32
1

@Matthew make sure your database compatibility level is set to 130 or higher, also check the examples in the documentation https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017 – SQLRaptor Jul 25 '19 at 19:30
oops - I was also missing a close parenthesis character in my example, fixed now... – SQLRaptor Jul 25 '19 at 19:31
Compatibility level change fixed it. Thanks for your help!! – Matthew Jul 26 '19 at 01:12

score 0 · Answer 2 · answered Jul 25 '19 at 18:07

You can do it by executing the entire query storing it in a variable. Caution, as this type of code could be vulnerable to SQL injection if API uses a parameter provided by a user.

DECLARE @indices NVARCHAR(MAX) = 
'1,2,3'

DECLARE @query NVARCHAR(MAX) = 
'update thisTable set someColumn = 'data to update with'
 Where [i] in (' + @indices+ ')'

Exec (@query)

How to update multiple rows with primary key variable in SQL

2 Answers2