Clever Geek Handbook

Updating multiple rows using a list of ids

I need to update multiple rows in a table based on IDs. In the stored procedure, I create a varchar variable that contains a list of ids.

Now in the table I have records with IDs 1, 2. The varchar variable has a value 1,2

; so I expect the row to not be updated by the next request.

UPDATE mytbl
SET flag = 1
WHERE Id IN (IdList); -- Here IdList has value '1,2'

But here is the line with Id how is 2

updated. Calling the query also returns the same string. I tried to concatenate IdList

like "'1','2'"

, then it regenerates both rows (with IDs 1 and 2). The data type Id

is int

. Is there a way to store a list of integers?

+3


source to share


4 answers


Try the following:

UPDATE mytbl
SET flag = 1
WHERE @IdList LIKE CONCAT('%,',Id,',%');


Note that your varchar list @IdList

must start and end with a comma (for example ,1,2,20,30,

).

+3


source


just use the identifiers as integers:



UPDATE mytbl
SET flag = 1
WHERE id in (1,2,3,....)
+7


source


Dynamically generate a subquery where the list is not a VARCHAR but part of the query text. That is, to implement it in a higher level language (perl / python / php).

0


source


You should try Left join as you get an @table type id parameter, it will give fast and reliable results :)

UPDATE 
    mytbl
SET
    flag = 1
FROM
    @IdList idL
    LEFT JOIN mytbl mTbl ON mTbl.ID = idL.Table_ID
0


source






More articles:


All Articles