Mysql: what to use instead of WITH statement or TEMPORARY tables?

January 8, 2022

Is there a way to store a select query into a table? I usually work with the WITH statement
but this is only possible with mysql 8+. I am currently using mysql 5.1. Therefore I tried to implement a temporary table but it is not possible to refer to a temporary table more than once in the same query (like the query below).

CREATE TEMPORARY TABLE test
SELECT  TIMESTAMPDIFF(MONTH, MAX(L.period), CURDATE()) as timediff, L.contractID 
FROM license L GROUP BY L.contractID;

UPDATE `contract` AS C
SET C.valid = CASE
WHEN (SELECT timediff FROM test AS T WHERE T.contractID = C.contractID) < 1 THEN 2
WHEN (SELECT timediff FROM test AS T WHERE T.contractID = C.contractID) = 1 THEN 1
WHEN (SELECT timediff FROM test AS T WHERE T.contractID = C.contractID) > 1 THEN 0
END;

DROP TEMPORARY TABLE test;

>Solution :

You could immediately remedy your problem by phrasing the update as a join:

UPDATE contract c
INNER JOIN test t
    ON t.contractID = c.contractID
SET valid = CASE WHEN t.timediff < 1 THEN 2
                 WHEN t.timediff = 1 THEN 1
                 ELSE 0 END;

This approach gets around the problem of referring to the temporary table more than once.