Q:

MySQL TRIGGER with an IF statement determined by an IN

I'am constructing a trigger
CREATE TRIGGER `address_control_update` BEFORE UPDATE ON `orders_shipping`
FOR EACH ROW 
BEGIN

    SET @error_msg_id := (SELECT `error_msg_id` FROM `orders_err` WHERE orderID = OLD.orderID);

    IF(LENGTH(NEW.ShippingName) = 0 OR NEW.ShippingName IS NULL) THEN 
        INSERT INTO `orders_err` (orderID,error_msg_id) VALUES(NEW.orderID,100) ON DUPLICATE KEY UPDATE complete=false;
    ELSEIF (100 IN (SELECT @error_msg_id))  THEN 
        UPDATE `orders_err` SET complete = true WHERE orderID = OLD.orderID AND error_msg_id = 100;
    END IF;
END;//

Where I would like to, first see if there is any error_msg_id on the orderID and later use this in the elseif statement to see if a possible error has been completed. 
It works if i do this
IF(LENGTH(NEW.ShippingName) = 0 OR NEW.ShippingName IS NULL) THEN 
    INSERT INTO `orders_err` (orderID,error_msg_id) VALUES(NEW.orderID,100) ON DUPLICATE KEY UPDATE complete=false;
ELSEIF EXISTS(SELECT * FROM orders_err WHERE orderID = OLD.orderID AND error_msg_id = 100)  THEN 
    UPDATE `orders_err` SET complete = true WHERE orderID = OLD.orderID AND error_msg_id = 100;
END IF;

But i would like to only call the select once if possible, since i have 12 similar if statements on other columns in the orders_shipping table.
On update i get the error: ERROR 1242: 1242: Subquery returns more than 1 row
Thanks in advance

A:

One of possible solutions is to concatenate ids to one string separated by commas using GROUP_CONCAT(..) function - then first SELECT statement will return one row. To test whether an id is in the string use FIND_IN_SET(..) function:
CREATE TRIGGER `address_control_update` BEFORE UPDATE ON `orders_shipping`
FOR EACH ROW 
BEGIN
    SET @error_msg_ids = (
      SELECT GROUP_CONCAT(DISTINCT `error_msg_id`)
        FROM `orders_err`
        WHERE orderID = OLD.orderID
        GROUP BY orderID);

    IF(LENGTH(NEW.ShippingName) = 0 OR NEW.ShippingName IS NULL) THEN 
        INSERT INTO `orders_err` (orderID,error_msg_id) VALUES(NEW.orderID,100) ON DUPLICATE KEY UPDATE complete=false;
    ELSEIF (FIND_IN_SET(100, @error_msg_ids))  THEN 
        UPDATE `orders_err` SET complete = true WHERE orderID = OLD.orderID AND error_msg_id = 100;
    END IF;
END;//

Also I think there exists other possibilities. For example you can write code without SELECT statement:
IF(LENGTH(NEW.ShippingName) = 0 OR NEW.ShippingName IS NULL) THEN 
    INSERT INTO `orders_err` (orderID,error_msg_id) VALUES(NEW.orderID,100) ON DUPLICATE KEY UPDATE complete=false;
ELSE
    UPDATE `orders_err` SET complete = true WHERE orderID = OLD.orderID AND error_msg_id = 100;
END IF;

why - because you use the same filtering conditions in SELECT and in UPDATE - so there no need to test the same conditions before update.

