6

I'm receiving an error ERROR 1137 (HY000) at line 9: Can't reopen table: 'temp_table' when executing a query similar to the following:

USE database_name;

CREATE TEMPORARY TABLE temp_table (status varchar(20));
INSERT INTO temp_table (status)
    SELECT status
    FROM client_contractor_status
    WHERE type = 'process';

SELECT table1.col1,
       table1.status,
       (SELECT
            COUNT(*)
        FROM
            table2
        RIGHT OUTER JOIN
            temp_table
            ON table2.status = temp_table.status
        WHERE table2.col1 = table1.col1
        ) AS counter
FROM
    table1

RIGHT OUTER JOIN
    temp_table
    ON table1.status = temp_table.status

I'm (just about) aware of the limitation that says you can't access a temporary table by two different aliases, however I haven't aliased it at all.

Is it being aliased automagically because it's in a sub-query? And if so, how can this be fixed?

Thanks.

Sean Airey
  • 235
  • 1
  • 2
  • 6

2 Answers2

7

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> 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.

Andriy M
  • 22,983
  • 6
  • 59
  • 103
  • Ok thanks, I had read about the aliases thing somewhere else but hadn't seen this page. That's quite a nasty limitation but ah well... – Sean Airey Jun 26 '13 at 11:08
1

You can always use another temporary table as a buffer in your 1st query and then use that buffer table to re-populate it back. eg:

-- Insert data into a temp buffer table
insert into tmp_buffer_table(id, name) 
        select per_t.id, per_t.name from tmp_table t, permanent_table per_t where per_t.id = t.id;
-- copy data form the temp buffer table to actual temp table
insert into tmp_table(id, name) select id, name from tmp_buffer_table;
-- reset the buffer table
truncate table tmp_buffer_table;
Abhijit
  • 111
  • 1