Duplicate Temporary Table MySQL

Question

I want to reference a temporary table multiple times in the same query. Due to the problem with temporary tables I cannot do that:

http://dev.mysql.com/doc/refman/5.7/en/temporary-table-problems.html

So to fix that I'm creating duplicates of the temporary table:

CREATE TEMPORARY TABLE IF NOT EXISTS earnings_temp_one AS (SELECT * FROM earnings WHERE earning_account_id = ?);
CREATE TEMPORARY TABLE IF NOT EXISTS earnings_temp_two AS (SELECT * FROM earnings WHERE earning_account_id = ?);
CREATE TEMPORARY TABLE IF NOT EXISTS earnings_temp_three AS (SELECT * FROM earnings WHERE earning_account_id = ?);

But this requires me to query the massive table earnings 3 times, which to some extent defeats the purpose.

Is there a way for me to duplicate a temporary table, under a different name?

Then I could run the query on the massive table just once.

Or maybe there's a fix to the temporary table problem that doesn't require me to create duplicates?

Answer 1

Ok, I solved it:

CREATE TEMPORARY TABLE IF NOT EXISTS earnings_temp_one AS (SELECT * FROM earnings WHERE earning_account_id = ?);
CREATE TEMPORARY TABLE IF NOT EXISTS earnings_temp_two AS (SELECT * FROM earnings_temp_one);
CREATE TEMPORARY TABLE IF NOT EXISTS earnings_temp_three AS (SELECT * FROM earnings_temp_one);