Join the same table temporary table table in MySQL

Question

I like to join a temporary table in MySQL, which fails, the idea is pretty 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: Error caused by mysql: ERROR 1137 (HY000): Can't reopen table: 'temp_table'

Answer 1

You cannot reference the 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

It works?

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

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

EDIT:

If with "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 without rows. Then you should fill it with insert . Or use create table only with select statement.

Answer 3

That should work. Just make sure your new table has a different name and 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 ;