3

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 !

Improve this question
4
  • 3
    You're just running into a limitation of MySQL. In similar situations I tend to do CREATE TEMPORARY TABLE temptable SELECT <your select here> and then join the UPDATE against that temporary table instead of the actual one. Commented Sep 15, 2012 at 21:23
  • The query engine can't figure out how to execute the query, it's bumped into itself, hence the silly message. Seen it with other DBMS's as well. As Ianzz suggests you need to simplify it by breaking it up. A temporary table would be good way to go, or rethink your schema to make this easier. Commented Sep 15, 2012 at 21:33
  • @TonyHopkinson: on which other DBMS did you see this kind of error? I didn't think there were other DBMS with this silly limit out there. Commented Sep 15, 2012 at 21:40
  • Doubt it was the exact same problem, but I've managed to confuse SQL Server and Sybase before now. Or maybe it was me who was confused. :p To be honest I wouldn't write a query like this anymore, and would use a temp table or a CTE, so I could understand never mind the DBMS. Commented Sep 15, 2012 at 22:08

1 Answer 1

Reset to default
3

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.