lundi 30 janvier 2017

MYSQL Subquery returns more than 1 row Triggers after update

I try to make one triggers to auto change some of my data everything is okay mysql accept my triggers but after that when i try to update on data showing me that error

Subquery returns more than 1 row

First my database little huge about this i'm copy here what i select with limit 3

My trigger is

DELIMITER $$
CREATE TRIGGER test3 AFTER UPDATE ON `coupons_data_result`
BEGIN
DECLARE mac INT default 0;
DECLARE durum INT default 0;
DECLARE ms1 INT default 0;

SET mac =(SELECT match_guess_type_id FROM coupon_rows WHERE match_code=NEW.cpnd_benzersiz);
SET durum =(select cpnd_status FROM matchs_result where cpnd_benzersiz=NEW.cpnd_benzersiz);
SET ms1 =(select cpnd_macsonucu1 FROM coupons_data_result where cpnd_benzersiz=NEW.cpnd_benzersiz);

IF (durum=3) THEN
    IF(mac=1)THEN
        IF(ms=1)THEN
            UPDATE  coupon_rows SET match_result='1' WHERE match_code=NEW.cpnd_benzersiz;
        Else
            UPDATE  coupon_rows SET match_result='0' WHERE match_code=NEW.cpnd_benzersiz;
        END IF;
    END IF;
END IF;

END$$

DELIMITER ;

What i select on my database with limit 3

+---------------------+-------------+
| match_guess_type_id | match_code  |
+---------------------+-------------+
|                   3 | 20170130398 |
|                   0 | 20170130399 |
|                   2 | 20170130401 |
+---------------------+-------------+
3 rows in set (0.00 sec)

+-------------+----------------+
| cpnd_status | cpnd_benzersiz |
+-------------+----------------+
|           3 | 20170129312    |
|           3 | 20170129313    |
|           3 | 20170129314    |
+-------------+----------------+
3 rows in set (0.00 sec)

+-----------------+----------------+
| cpnd_macsonucu1 | cpnd_benzersiz |
+-----------------+----------------+
|               0 | 20170129312    |
|               1 | 20170129313    |
|               1 | 20170129314    |
+-----------------+----------------+
3 rows in set (0.00 sec)

End what i try to update list

+--------------+-------------+
| match_result | match_code  |
+--------------+-------------+
|            1 | 20170130398 |
|            3 | 20170130399 |
|            3 | 20170130401 |
+--------------+-------------+
3 rows in set (0.00 sec)

Ps: All match_code and benzersiz is on the table

Aucun commentaire:

Enregistrer un commentaire