Subquery returns more than one value

Question

When performing an update within a precedent, the message appears saying that the comparison subquery brings more than one result. The subquery has been tested and brings only one result, since it searches for the Exam id and this is unique for each row of 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 Exame code parameter is an int passed in the Procedure call, and therefore corresponds to a single value. I have already checked the values entered in the table Exam, and do not have lines with repeated codes.

Answer 1

Beyond what the message itself suggests, I can’t imagine another reason why this error is being triggered.

However, you can circumvent 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’ve helped.