Marco Antonio Marco Antonio - 14 days ago 9
MySQL Question

Mysql ERROR: 1093

Help me pls.
the exercise is:
"Modify the database so that all players who have scored more than 3 goals per match earning a 5% increase in your salary."
How do I do this? I tried with this code but he Is giving error

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)


table structure:
http://textuploader.com/d5vlh

Error Code: 1093. You can't specify target table 'tbjogador' for update in FROM clause 0.000 sec

Answer

You can't update a table used for selecting the value you need but you can force mysql to create dinamically a temporary table for this

    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 ) 
Comments