Subquery returns more than one value

Question

Subquery returns more than one value

Navigation

#1 by (0 votes)

1

When performing an update within a procedure, the message appears saying that the comparison subquery brings more than one result. The subquery has been tested and only returns one result since it searches for the Exam id and this is unique for each row in the table.

DELIMITER $$
CREATE PROCEDURE realizaAprovacao (IN codigoExame INT)
    BEGIN

        IF(SELECT nota FROM Exame WHERE idExame = codigoExame) >= 60 THEN
            UPDATE Exame SET statusAprovacao = TRUE WHERE idExame = codigoExame;
        ELSE 
            UPDATE Exame SET statusAprovacao = FALSE WHERE idExame = codigoExame;
        END IF;
    END
$$

The parameter CodeExame is an int passed in the procedure call, and therefore corresponds to a single value. I already checked the values entered in the Exam table, and do not have lines with repeated codes.

mysql query stored-procedures

asked by anonymous 13.05.2018 / 16:37

1 answer

href with target blank in jQuery How to use different implementations of the same interface

score 0 · Accepted Answer

In addition to the message itself suggests, I can not imagine another reason for this error to be triggered.

However, you can work around this in other ways.

For example:

Put a Limit 1

IF(SELECT nota FROM Exame WHERE idExame = codigoExame LIMIT 1) >= 60 THEN

Do not evaluate. Simply change

UPDATE Exame
SET statusAprovacao = CASE WHEN nota >= 60 THEN true ELSE false END
WHERE idExame = codigoExame;

I hope I have helped.