CODEWITHSUNDEEP
mysqluniontemp-tables
Chris Pillen
Chris Pillen

Reputation: 828

How can I refer to a TEMPORARY table more than once in the same query?

the MySQL docs say: "You cannot refer to a TEMPORARY table more than once in the same query."

I know this has been asked before. But I can't find a specific solution for the following.

I'm doing a preselection into a temporary table

CREATE TEMPORARY TABLE preselection AS SELECT ...;

now I wanna do some (around 20 or even 30) unions

(SELECT FROM preselection ...)
UNION
(SELECT FROM preselection ...)
UNION
......
UNION
(SELECT FROM preselection ...)

I could make 20 or 30 copies of preselection and do each select on each table but if I understand it right this is the same as invoke the preselection-query above in every SELECT inside the UNION chain as a subquery.

Is there a way to work around this issue?

Greetings,

chris

Full query:

CREATE TEMPORARY TABLE preselection AS
(
SELECT id, title, chapter, date2, date, snid, max(score) FROM `movies`

WHERE 
(
cluster is not NULL
) 
AND 
(
`date` <= '2012-02-20 05:20:00'
AND `date` > '2012-02-19 17:20:00' 
AND (TIMEDIFF(date, date2) < '12:00:00')
)
GROUP BY cluster
)
UNION
(
SELECT id, title, chapter, date2, date, snid, score FROM `movies`
WHERE cluster IS NULL
AND
(
`date` <= '2012-02-20 05:20:00' AND `date` > '2012-02-19 17:20:00' AND (TIMEDIFF(date, date2) < '12:00:00')
)
);

(SELECT * FROM preselection WHERE snid=1 AND chapter LIKE '#A_OT%'
 DESC LIMIT 4)
UNION
 … 
UNION 
(SELECT * FROM preselection WHERE snid=19 AND chapter LIKE '#A_OT%' 
 LIMIT 4)
UNION
 ... for each chapter from A to J and every snid from 1 to 19 ...
UNION
(SELECT * FROM preselection WHERE snid=1 AND chapter LIKE '#J_OT%'
 LIMIT 4)
UNION
 … 
UNION 
(SELECT * FROM preselection WHERE snid=19 AND chapter LIKE '#J_OT%' 
LIMIT 4)

ORDER BY `score` DESC, `date`;

Upvotes: 6

Views: 8397

Answers (2)

KCD
KCD

Reputation: 10281

Yes it can be frustrating. The solutions I typically use involve avoiding the temporary table with a more complex query or using more temporary tables.

A simple copy (but like you mentioned this is no use to you)

CREATE TEMPORARY TABLE preselectionCopy AS
SELECT * FROM preselection;

Or a temporary table to hold your results

CREATE TEMPORARY TABLE result (
    id INT NULL,
    title VARCHAR(256) NULL
    ...
    );

...which can be as simple as

CREATE TEMPORARY TABLE result AS
SELECT * FROM preselection WHERE snid=1 AND chapter LIKE '#A_OT%' 
LIMIT 4;

INSERT INTO result
SELECT * FROM preselection WHERE snid=19 AND chapter LIKE '#A_OT%' 
 LIMIT 4;

...

SELECT * FROM result 
ORDER BY `score` DESC, `date`;

Upvotes: 2

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

I think the error message is clear: you can't do that with a single temporary table. Does creating a view of the data, instead of a temporary tables, do the trick?

Views in mysql

Upvotes: 4

Related Questions