Clever Geek Handbook

Delete multiple rows based on selection

I am trying to make a trigger that will delete rows from one table based on a selection 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;

The problem is that there can be multiple tags for one position, so the subquery will return multiple rows. How do I change this so that it works with multiple tags?

+3


source to share


3 answers


If I understand you want to remove all tags.
so try replacing your = before the subqueryIN 



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;
+3


source 


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;


Going from =

to IN

should work on subqueries returning multiple rows

+3


source


A more efficient way to do this 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;
+3


source






More articles: