CODEWITHSUNDEEP
mysqlsubquery
Wabbit
Wabbit

Reputation: 149

MySQL: sub query returns more than one row

I need to delete rows from responses table after the questions table has been updated but I get this error: sub query returns more than one row. Is there a way around to get it working?

CREATE TRIGGER delete_responses AFTER UPDATE ON  questions
FOR EACH ROW 
BEGIN 
IF NEW.active != OLD.active 
THEN DELETE FROM responses WHERE option_id = (
SELECT option_id
FROM options
WHERE question_id = OLD.question_id);

Upvotes: 0

Views: 866

Answers (4)

CloudyMarble
CloudyMarble

Reputation: 37566

You can either limit the results of your subquery to 1 by limit or distinct, or if you ened to delete all founded results in the subquery you need to use WHERE id IN (....)

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Yes, use JOIN instead:

DELETE r
FROM responses AS r
INNER JOIN options AS o ON r.option_id = o.option_id
WHERE o.question_id = OLD.question_id;

Upvotes: 1

Borniet
Borniet

Reputation: 3546

Use "in":

CREATE TRIGGER delete_responses AFTER UPDATE ON  questions
FOR EACH ROW 
BEGIN 
IF NEW.active != OLD.active 
THEN DELETE FROM responses WHERE option_id in (
SELECT option_id
FROM options
WHERE question_id = OLD.question_id);

or restrict your subquery so that it only returns one row.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Change the = to = ANY or in:

CREATE TRIGGER delete_responses AFTER UPDATE ON  questions
FOR EACH ROW 
BEGIN 
IF NEW.active != OLD.active 
THEN DELETE FROM responses WHERE option_id in (
SELECT option_id
FROM options
WHERE question_id = OLD.question_id);

Upvotes: 0

Related Questions