CODEWITHSUNDEEP
phpmysqlstored-procedures
Prasad Patel
Prasad Patel

Reputation: 747

how to use IN operator in MySQL stored procedure?

I am trying to update multiple records in MySQL table but its not working only the first record is getting updated. I am using MySQL 'IN' for updating mutiple records.

My Code:

CALL  my_proc_name('','','','','','','','','','','','','','',$modifiedby,$modifiedon,'$status','$type','$ids','$start','$limit','$remarks');

My SP is:

UPDATE my_table_name SET modifiedby=modifiedby1,modifiedon=modifiedon1,status=status1 WHERE leaveid IN (id);

Parameters are:

$ids = 2016,2017,2019,2020
$status = 'Approved'

My problem here is only the first record is getting updated, in this case as per my above parameters only id 2016 is updated. What is my mistake here? My question here is can I give opening ('(') & closing (')') braces directly for my table column 'id' like "IN (id)" to stored procedure?

Upvotes: 1

Views: 1002

Answers (1)

Prasad Patel
Prasad Patel

Reputation: 747

Finally, I found a solution. "FIND_IN_SET" has solved my problem, but the row id's which I update must be declared as "varchar" in my Stored Procedure(SP) here that is my column 'id' should be "varchar" in SP.

Below is my modified SP:

UPDATE my_table_name SET modifiedby=modifiedby1,modifiedon=modifiedon1,status=status1 WHERE  FIND_IN_SET(leaveid,id);

Upvotes: 2

Related Questions