MySQL ERROR 1137 (HY000) at line 9: Can't reopen table: 'temp_table'
This is a known and well documented issue:


  
  You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work:

mysql&gt; SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'

  
  This error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function.
  


One workaround that comes to mind would be to use a "normal" table as a temporary storage. You would probably need to use a session identifier to make the approach work in a multi-user environment.