MySQL Select Update 引导，查询不执行

Question

这不起作用，我收到以下错误。我知道为什么，因为我无法在同一张表上进行选择和更新。有人可以帮助/指导我正确的方向吗？

update 
    episode 
set 
    update_wp = 1 
where 
    episode_id in(
        select 
            e.episode_id 
        from 
            collection c 
        inner join 
            season s on s.collection_id = c.collection_id 
        inner join 
            episode e on e.season_id = s.season_id  
        where 
            c.title ='mom'
        );

MySQL 响应:

ERROR 1093 (HY000): You can't specify target table 'episode' for update in FROM clause

Answer 1

我宁愿使用以下查询:

UPDATE episode e, collection c, season s 
   SET e.update_wp = 1 
   WHERE 
     e.season_id = s.season_id AND 
     s.collection_id = c.collection_id AND 
     c.title = 'mom';