Delete multiple rows based on select

Question

I'm trying to make a trigger that will remove rows from one table based on select in another table

table operatorpositions columns operator, line, pos

table positiontags columns line, position, tag

table operatortags columns operator, tag

My trigger looks like this

CREATE TRIGGER removeOperatorPosition AFTER DELETE ON operatorpositions
    FOR EACH ROW
    BEGIN
        DELETE FROM operatortags WHERE gen = NEW.operator 
            AND tag = (SELECT tag FROM positiontags WHERE position = NEW.pos AND line = NEW.line);            
    END;

Problem is there can be multiple tags for one position, so subquery will return multiple rows. How do I need to change this so it will work with multiple tags?

Dor Cohen · Accepted Answer · 2012-03-30 11:42:48Z

3

If I understood you you want to delete all tags.
so try to replace your = before the subquery with IN

CREATE TRIGGER removeOperatorPosition AFTER DELETE ON operatorpositions
    FOR EACH ROW
    BEGIN
        DELETE FROM operatortags WHERE gen = NEW.operator 
            AND tag IN (SELECT tag FROM positiontags WHERE position = NEW.pos AND line = NEW.line);            
    END;

answered Mar 30, 2012 at 11:42

Dor Cohen

17.1k24 gold badges97 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sathyajith Bhat · Accepted Answer · 2012-03-30 11:43:44Z

3

CREATE TRIGGER removeoperatorposition AFTER DELETE
ON operatorpositions
FOR EACH ROW
BEGIN
  DELETE FROM operatortags
  WHERE  gen = new.operator
         AND tag IN (SELECT tag
                     FROM   positiontags
                     WHERE  position = new.pos
                            AND line = new.line);
END;

Changing from = to IN operator should work on subqueries returning multiple rows

answered Mar 30, 2012 at 11:43

Sathyajith Bhat

22k22 gold badges102 silver badges141 bronze badges

1 Comment

Milan Halada Over a year ago

Yep, feel stupid it was so easy :) Also had to change new to old because im deleting

user1191247 · Accepted Answer · 2012-03-30 11:49:24Z

3

A more efficient way to do it is to use a join -

CREATE TRIGGER removeOperatorPosition AFTER DELETE ON operatorpositions
    FOR EACH ROW
    BEGIN
        DELETE ot
        FROM operatortags ot
        INNER JOIN positiontags pt
            ON ot.tag = pt.tag
        WHERE ot.gen = OLD.operator 
        AND pt.position = OLD.pos
        AND pt.line = OLD.line;            
    END;

answered Mar 30, 2012 at 11:49

user1191247

13k2 gold badges25 silver badges33 bronze badges

Collectives™ on Stack Overflow

Delete multiple rows based on select

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related