mysql - UPDATE in Procedure does not work -

i have 2 tables: question , quiz.

quiz collection of questions.

quiz table looks this:

quizdid | questionids (nvarchar50)

  1      |   1,2,3,4,5   2      |   6,7,8,9 

question table has primary key id (integer), question , status field

i want write procedure update status of questions in question table.

the following query works in sql console

update questions set activestatus = 'x'  find_in_set(id, (select questionids quiz quizid = 2)) > 0 

4 rows updated here.

however, when put same query in stored procedure , execute parameters, not work.

stored procedure code:

create definer=`root`@`localhost`  procedure `update_quiz_status`(in `quizid` int, in `status` varchar(1)) modifies sql data sql security invoker update questions set activestatus = status  find_in_set(id, (select questionids quiz quizid = quizid)) > 0 

procedure has no syntax errors.

when call procedure sql console, error:

call update_quiz_status(2,'x'); 

error : #1242 - subquery returns more 1 row

i dont know why query works in sql console not in stored procedure !

is there other way can achieve ? i.e. update question status given quiz sql procedures.

your problem here

select questionids quiz quizid = quizid

in mind second quizid refers passed paramenter. parser, however, seeing quizid in quiz table , therefore returning rows table.

change name of parameter , should fine.