Join the same table temporary table in MySQL

Question

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'

Answer 1

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

Answer 2

This should work. Just make sure your new table has a different name then the existing one.

CREATE TEMPORARY TABLE new_table

SELECT p1,p2,p3 FROM existing_table WHERE p4 = 1

UNION 

SELECT p1,p2,p3 FROM existing_table WHERE p4 = 2
;

Answer 3

Does this work?

SELECT p1, p2, p3
FROM temp_table
WHERE p4 in (1, 2);

It is a much simpler way to write the same query.

EDIT:

If by "fail" you mean "doesn't return any rows", then you have a simple problem. CREATE TABLE LIKE does not populate the table. It creates a table with the same structure as any_other_table, but with no rows. You should then populate it with insert. Or, use create table with the select statement only.

Answer 4

Creating copies of the temporary table seems to work and may be a viable alternative approach:

CREATE TEMPORARY TABLE t2 LIKE temp_table;
INSERT INTO t2 SELECT * FROM temp_table;

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