CODEWITHSUNDEEP
mysqlsql
benjamin54
benjamin54

Reputation: 1300

Updating multiple rows using list of Ids

I need to update multiple rows in the table on the basis of Ids. In the stored procedure, I'm creating a varchar variable that holds the Ids list.

Now, in the table I have entries with Ids 1, 2. The varchar variable has value 1,2; so I'm expecting no row to be updated with following query.

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

But here row with Id as 2 is getting updated. Select query also returns the same row. I tried concatenating IdList as "'1','2'", then it retuens both the rows (with Ids 1 and 2). The data type of Id is int. Is there proper way to maintain integer list?

Upvotes: 8

Views: 20179

Answers (5)

Dinushika Rathnayake
Dinushika Rathnayake

Reputation: 419

This also works, here you don't need to have comma at the start and the end of defined variable.

SET @IdList = "1,2";

UPDATE mytbl
SET flag = 1
WHERE FIND_IN_SET(Id, @IdList);

Upvotes: 0

nitin
nitin

Reputation: 156

You Should try Left join as you are getting a @table type parameter of IDs this will give fast and more reliable result :)

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

Upvotes: 0

i486
i486

Reputation: 6563

Try this:

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

Note that your varchar list @IdList must start and end with comma (e.g. ,1,2,20,30,).

Upvotes: 6

i486
i486

Reputation: 6563

Dynamically generate subquery where the list in not VARCHAR but part of text of query. I.e. implement this in higher level language (perl/python/php).

Upvotes: 0

Olli
Olli

Reputation: 1738

just use the ids as integers like so:

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

Upvotes: 13

Related Questions