Referring to a TEMPORARY Table More Than Once in the Same Query in MySQL

In MySQL you cannot refer to a TEMPORARY table more than once in the same query, that's why it requires significant code changes.

Let's consider the following piece of code:

 create procedure self_join_proc
 as
 begin
   create table #temp_t1(a int, b int)
 
   insert into #temp_t1 values(0,0);
   insert into #temp_t1 values(0,1);
   insert into #temp_t1 values(1,1);
   insert into #temp_t1 values(1,2);
 
   select t1.a, t1.b, t2.a, t2.b from #temp_t1 t1, #temp_t1 t2 where t1.a=t2.b;
 end;

For this case there is the following solution: it can solved by creating a similar temporary table.

First need to check if there is a temporary table with the name tt_temp_t12 and if it already exists, delete it. Then we create tt_temp_t12 temporary table and fill it with data from the tt_temp_t1 table. In the select statement, instead of reusing the tt_temp_t1 table, we will use the newly created tt_temp_t12 table.

If the temporary table is used three times in one query, then two additional temporary tables will be created, if four times - three additional temporary tables will be created, and so on. This way we will avoid referring to a temporary table more than once in the same query.

You will get the following result:

   create PROCEDURE self_join_proc()
   begin
     DROP TEMPORARY TABLE IF EXISTS tt_temp_t1;
     create TEMPORARY table tt_temp_t1
     (
       a INT, 
       b INT
     );
 
     insert into tt_temp_t1  values(0,0);
     insert into tt_temp_t1  values(0,1);
     insert into tt_temp_t1  values(1,1);
     insert into tt_temp_t1  values(1,2);
    
    
     DROP TEMPORARY TABLE IF EXISTS tt_temp_t12;
     CREATE TEMPORARY TABLE tt_temp_t12 select * from tt_temp_t1;
     select t1.a, t1.b, t2.a, t2.b from tt_temp_t1 t1, tt_temp_t12 t2 where t1.a = t2.b;
 end;

Let's compare the results:

Microsoft SQL Server	MySQL
exec self_join_proc	CALL test.self_join_proc()

As you can see, procedures return the same result set.

This solution is automatically executed by our software Ispirer Toolkit. You can learn more about other features of the conversion toolkit, as well as try out a demo license on our website.

If you have any questions or face any difficulties, please contact our support team: support@ispirer.com