mysql - 为多个更新查询创建存储过程

Question

select id as ids from challenges where expire_date > today

我需要循环她来乘以 id，例如(php 中的 foreach)

foreach (ids) 

    update challenges set status = 'expired' where id = ids

    update user_challenge set challenge_status = 'Expired' where challenge_status = 'Pending' and challenge_id = ids 
    update user_challenge set challenge_status = 'Failed' where challenge_status = 'Accepted' and challenge_id = ids 

end for each;

任何人都可以创建存储过程或单个查询来执行此操作，谢谢

Answer 1

MySql 中的单个更新查询可能如下所示

UPDATE user_challenge u INNER JOIN
       challenges c ON u.challenge_id = c.id
   SET c.status = 'Expired',
       u.challenge_status = CASE WHEN u.challenge_status = 'Pending' THEN 'Expired' 
                                 WHEN u.challenge_status = 'Accepted' THEN 'Failed' END
 WHERE c.expire_date < CURDATE()

这里是SQLFiddle 示例

我认为有效的到期条件应该是 expire_date < CURDATE() (意味着到期日期应该小于今天的日期)这反射(reflect)在查询中。