Menu
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?
497
There are a couple of ways to do it. INSERT INTO students (id, score1, score2) VALUES (1, 5, 8), (2, 10, 8), (3, 8, 3), (4, 10, 7) ON DUPLICATE KEY UPDATE score1 = VALUES(score1), score2 = VALUES(score2);
We can update the multiple rows of the table using the single update command.
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
just use the ids as integers like so:
UPDATE mytbl
SET flag = 1
WHERE id in (1,2,3,....)
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,).
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
