Search code examples
mysqlsqljointemp-tables

Join the same table temporary table in MySQL

I like to join a temporary table in MySQL which fails, the idea quite simple:

CREATE TEMPORARY TABLE temp_table LIKE any_other_table; -- srsly it does not matter which table

(
  SELECT p1,p2,p3 FROM temp_table WHERE p4 = 1
) UNION (
  SELECT p1,p2,p3 FROM temp_table WHERE p4 = 2
)

Any help is greatly appreciated.

EDIT: The error thrown by mysql is ERROR 1137 (HY000): Can't reopen table: 'temp_table'

Solution

  • You cannot refer to a TEMPORARY table more than once in the same query.

    Please read the following link http://dev.mysql.com/doc/refman/5.5/en/temporary-table-problems.html