MySQL 错误 : 1093

Question

请帮帮我。练习是:“修改数据库，让所有每场比赛进球超过 3 个的球员的薪水增加 5%。”我该怎么做呢？我尝试使用这段代码，但他给出了错误

 update tbplayer
 set salary_player=(salary_player*1.0)
 where tbplayer.name_player in (
 select tbjplayer.name_player
 from tbjplayer inner join tbplay
 on tbplayer.cod_player = tbplay.cod_player
 where tbplay.numberGoalsPlayer >= 3)   

表结构: http://textuploader.com/d5vlh

错误代码:1093。您不能在 FROM 子句 0.000 秒中指定要更新的目标表 'tbjogador'

Answer 1

您不能更新用于选择所需值的表，但您可以强制 mysql 为此动态创建一个临时表

    update tbplayer

      set salary_player=(salary_player*1.0)
      where tbplayer.name_player in ( select t.my_name  from ( 

            select tbjplayer.name_player my_name
            from tbjplayer inner join tbplay
            on tbplayer.cod_player = tbplay.cod_player
            where tbplay.numberGoalsPlayer >= 3)  t )