Mysql #1235 error code, when create 2 triggers with different purposes

Question

I have a trigger that created in phpmyadmin, like :

BEGIN
UPDATE bands SET 
bands.status = "active"
where bands.id IN(
SELECT * FROM
(select bands.id 
from bands where bands.id IN 
(select bandId from invites join bands 
on(invites.bandId = bands.id) 
where invites.status="approved" 
group by bandId having count(invites.status)=bands.total_personel))AS X);
end

This trigger will changes my bands status to active.

But I want to change the status automatically to "inactive" too, if it doesn't meet the where clause.

If I create another trigger like :

BEGIN
UPDATE bands SET 
bands.status = "inactive"
where bands.id IN(
SELECT * FROM
(select bands.id 
from bands where bands.id IN 
(select bandId from invites join bands 
on(invites.bandId < bands.id) 
where invites.status="approved" 
group by bandId having count(invites.status)=bands.total_personel))AS X);
end

Mysql gives me #1235 error code.

How can I resolve this problem? or is there a way to combine these purposes in just one trigger?

Answer 1

I have found the answer. I don't know before that UPDATE command can be combined with WHEN condition to.

Here the code :

BEGIN
UPDATE bands SET status = CASE
WHEN bands.id IN
(SELECT * FROM
(select bands.id from bands where bands.id IN 
(select bandId from invites join bands 
 on(invites.bandId = bands.id) 
 where invites.status="approved" 
 group by bandId having count(invites.status)=bands.total_personel))AS X) 
 then "active"
 ELSE "inactive"
 END
 WHERE bands.id in (select distinct invites.bandId from invites);
 END

That Mysql code changes the status to active if meets the when condition, and automatically changes the status to inactive if it doesn't meet the WHEN condition.