Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
310 views
in Technique[技术] by (71.8m points)

mysql - UPDATE in Procedure does not work

I have 2 tables: Question and Quiz.

Quiz is a collection of Questions.

Quiz table looks like this:

QuizdId | QuestionIds (NVARCHAR50)

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

Question table has primary key ID (integer), Question and Status field

I want to write a procedure to update the status of all the questions in Question Table.

The following Query works in SQL Console

UPDATE questions SET ActiveStatus = 'X' 
WHERE FIND_IN_SET(ID, (SELECT QuestionIds from quiz where QuizId = 2)) > 0

4 rows are updated here.

However, when I put the same query in a stored procedure and execute it with parameters, it does 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 
WHERE FIND_IN_SET(ID, (SELECT QuestionIds from quiz where QuizId = QuizId)) > 0

Procedure has no syntax errors.

When I call the procedure from SQL console, I get the error:

call update_quiz_status(2,'X');

Error : #1242 - Subquery returns more than 1 row

I dont know why the query works in SQL console but not in stored procedure !

Is there any other way I can achieve this ? i.e. update the question status for a given quiz by SQL procedures.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Your problem is here

SELECT QuestionIds from quiz where QuizId = QuizId

In you mind the second QuizId refers to the passed paramenter. The parser, however, is seeing that as the QuizId in the quiz table and is therefore returning all rows from that table.

Change the name of your parameter and you should be fine.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...