I've seen a couple of questions about this particular error message, but couldn't find any that matches my problem.
Just a quick explanation of my problem.
I have a table containing the results for some games.
When someone is absent, we store the average of this person for the previous games as the game result and we set the property f_present to 0.
For that part no problem.
The problem occurs when we modify an old game, I have to update all following games where someone is absent to store a new value (the new average of that player for the game he is absent.)
I'm able to do the query, but I'm unable to do the update because of the "You can't specify target table for update in FROM table" error message.
Here's is my query (which works)
select t1.f_subgame_id,
t1.F_PLAYER_ID,
(SELECT avg(t2.f_result)
from t_subgames_results t2
join t_subgames t2s on t2s.f_subgame_id = t2.f_subgame_id
join t_seasons_games t2ss on t2ss.f_game_id = t2s.f_game_id
where F_RESULT > -1
and F_PLAYER_ID = t1.F_PLAYER_ID
and t2ss.F_SEASON_ID = t1ss.F_SEASON_ID
and t2ss.F_DATE < t1ss.F_DATE
)
from t_subgames_results t1
join t_subgames t1s on t1s.f_subgame_id = t1.f_subgame_id
join t_seasons_games t1ss on t1ss.f_game_id = t1s.f_game_id
where t1ss.f_date>'2012-09-07' and t1.F_PRESENT = 0;
Here's my attempt to update:
update t_subgames_results t1
join t_subgames t1s on t1s.f_subgame_id = t1.f_subgame_id
join t_seasons_games t1ss on t1ss.f_game_id = t1s.f_game_id
set f_result =
(SELECT avg(t2.f_result) as result
from t_subgames_results t2
join t_subgames t2s on t2s.f_subgame_id = t2.f_subgame_id
join t_seasons_games t2ss on t2ss.f_game_id = t2s.f_game_id
where F_RESULT > -1
and F_PLAYER_ID = t1.F_PLAYER_ID
and t2ss.F_SEASON_ID = t1ss.F_SEASON_ID
and t2ss.F_DATE < t1ss.F_DATE
)
where t1ss.f_date>'2012-09-07' and t1.F_PRESENT = 0;
Again, I know there are a couple of topics about this problem but can't seem to find that relate to my problem ...
Thanks again for helping me out !
I ended up with this, thanks a lot !
create temporary table t_results_temp SELECT * from t_subgames_results;
update t_subgames_results t1
join t_subgames t1s on t1s.f_subgame_id = t1.f_subgame_id
join t_seasons_games t1ss on t1ss.f_game_id = t1s.f_game_id
set f_result =
(SELECT COALESCE(avg(t2.f_result),0) as result
from t_results_temp t2
join t_subgames t2s on t2s.f_subgame_id = t2.f_subgame_id
join t_seasons_games t2ss on t2ss.f_game_id = t2s.f_game_id
where t2.F_RESULT > -1
and F_PLAYER_ID = t1.F_PLAYER_ID
and t2ss.F_SEASON_ID = t1ss.F_SEASON_ID
and t2ss.F_DATE < t1ss.F_DATE
)
where t1ss.f_date>'2012-09-07' and t1.F_PRESENT = 0;
drop table t_results_temp;