过程中的 UPDATE 不起作用 [英] UPDATE in Procedure does not work

问题描述

我有 2 个表:问题和测验.

I have 2 tables: Question and Quiz.

测验是一系列问题.

测验表如下所示:

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

问题表有主键 ID(整数)、问题和状态字段

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.

以下查询在 SQL 控制台中有效

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 行.

但是，当我将相同的查询放入存储过程并带参数执行时，它不起作用.

However, when I put the same query in a stored procedure and execute it with parameters, it does not work.

存储过程代码:

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

程序没有语法错误.

当我从 SQL 控制台调用该过程时，出现错误:

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

call update_quiz_status(2,'X');

错误:#1242 - 子查询返回超过 1 行

Error : #1242 - Subquery returns more than 1 row

我不知道为什么查询在 SQL 控制台中有效，而在存储过程中无效！

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

还有其他方法可以实现吗?即通过 SQL 程序更新给定测验的问题状态.

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

推荐答案

你的问题在这里

SELECT QuestionIds from quiz where QuizId = QuizId

在您看来，第二个 QuizId 指的是传递的参数.但是，解析器将其视为 quiz 表中的 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.

这篇关于过程中的 UPDATE 不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！