How can this permanent table be made temporary?

Question

I need to make the following permanent MySQL table temporary to avoid polluting the DB with tables like FINAL_RESULTS, which I use elsewhere in the query but do not want to persist.

CREATE TABLE FINAL_RESULTS(
ID VARCHAR(38),
DATE1 DATETIME,
DATE2 DATETIME,
PRIMARY KEY(ID,DATE1)
);

INSERT INTO FINAL_RESULTS(DATE1,DATE2)
SELECT DISTINCT t2.ID,DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S"))
FROM SOURCE1 AS t1
INNER JOIN SOURCE2 AS t2
ON t1.ID = t2.ID
WHERE t1.code IN ("a", "b")
AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) <= t2.DATE3
AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) >= t2.DATE4;

UPDATE FINAL_RESULTS
INNER JOIN (
    SELECT t2.PAT_ID, MIN(DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S"))) AS mindate
    FROM SOURCE1 AS t1
    INNER JOIN FINAL_RESULTS AS t2
    ON t1.ID = t2.ID
    WHERE t1.CODE IN ("c", "d")
    AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) >= DATE_SUB(t2.DATE1, INTERVAL 21 DAY)
    AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) <= DATE_ADD(t2.DATE1, INTERVAL 14 DAY)
    GROUP BY t1.ID
) AS x ON FINAL_RESULTS.ID = x.ID
SET DATE2=x.mindate;

The problem: if I simply use "CREATE TEMPORARY TABLE" here instead of "CREATE TABLE", I will get the "Can't reopen table 'FINAL_RESULTS'" error since MySQL only lets you refer to temporary tables once per query, which the last statement violates. The docs recommend using a common table expression to get around this, but I'm running MySQL 5.7.12 and CTEs only work in version 8.0.

Does anyone have any alternative solutions for version 5.7.12?

Thanks in advance!

Answer 1

CREATE TEMPORARY TABLE FINAL_RESULTS(
ID VARCHAR(38),
DATE1 DATETIME,
DATE2 DATETIME,
PRIMARY KEY(ID,DATE1)
);

INSERT INTO FINAL_RESULTS(DATE1,DATE2) -- ??? should be FINAL_RESULTS(ID, DATE1) ?
SELECT DISTINCT t2.ID,DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S"))
FROM SOURCE1 AS t1
INNER JOIN SOURCE2 AS t2
ON t1.ID = t2.ID
WHERE t1.code IN ("a", "b")
AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) <= t2.DATE3
AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) >= t2.DATE4;

-- convert the subquery to second temptable
CREATE TEMPORARY TABLE x
    SELECT t2.PAT_ID, -- ??? should be `AS id` ?
           MIN(DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S"))) AS mindate
    FROM SOURCE1 AS t1
    INNER JOIN FINAL_RESULTS AS t2
    ON t1.ID = t2.ID
    WHERE t1.CODE IN ("c", "d")
    AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) >= DATE_SUB(t2.DATE1, INTERVAL 21 DAY)
    AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) <= DATE_ADD(t2.DATE1, INTERVAL 14 DAY)
    GROUP BY t1.ID;

-- use second tempotable instead of the subquery
UPDATE FINAL_RESULTS
INNER JOIN x ON FINAL_RESULTS.ID = x.ID
SET DATE2=x.mindate;