MySQL ERROR 1137 (HY000) at line 9: Can't reopen table: 'temp_table'

6 votes

2 answers

10876 views

mysql-5.1

                          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.
                        

Asked by Sean Airey (235 rep)

Jun 26, 2013, 09:59 AM
Last activity: Feb 2, 2023, 07:37 AM

MySQL ERROR 1137 (HY000) at line 9: Can't reopen table: 'temp_table'

Related Questions