Converting T-SQL to MySQL with temp tables

Question

I am working on converting a T-SQL stored procedure into MySQL. I am not familiar with T-SQL and am working on becoming more familiar with temp tables and stored procedures. Thanks for your help in advance.

The T-SQL original looks like this (EDIT: note this is only a portion of the original procedure used to produce a report):

DROP TABLE IF EXISTS #accounts;

SELECT  d.data_id                   
        ,p.pp_name AS name
        ,CONVERT(tinyint,1) AS flag             
        ,d.pd_date
        ,CONVERT(char(6),pd_date,112) AS date_period                
        ,CONVERT(varchar(3),0) AS n_phones                          
INTO    #accounts                   
from    table_detail d                  
    JOIN    table_pool p ON d.pp_id = p.pp_id                   
    JOIN    table_type t ON p.pp_type_id = t.pp_type_id                                                     
    JOIN    Inventory i ON d.data_id = i.data_id                    
    JOIN    Product pr ON i.product_id = pr.product_id  
WHERE   pp_name IN (SELECT name FROM Sandbox..desired_sandbox)                  
    AND DATEDIFF(MONTH,pd_date,GETDATE()) < 3
            
                        
UPDATE  a                   
SET     a.flag = 0              
FROM    #accounts a                 
JOIN    table_detail d ON a.data_id = d.data_id                 
JOIN    table_pool p ON d.pp_id = p.pp_id                   
WHERE   d.pd_date < a.pd_date                   
    AND pp_name != 'error';

My current update is below. Do I need to wrap this in a CREATE TEMPORARY TABLE IF NOT EXISTS accounts AS (<insert query here>) instead of the INTO #accounts?

SELECT  d.data_id                   
        ,p.pp_name AS name                              
        ,CONVERT(tinyint,1) AS flag             
        ,d.pd_date
        ,DATE_FORMAT(pd_date,'%Y%m%d') AS date_period               
        ,CONVERT(varchar(3),0) AS n_phones                                  
FROM    table_detail d                  
    JOIN    table_pool p ON d.pp_id = p.pp_id                   
    JOIN    table_type t ON p.pp_type_id = t.pp_type_id                                 
    JOIN    Inventory i ON d.data_id = i.data_id                    
    JOIN    Product pr ON i.product_id = pr.product_id  
WHERE   pp_name IN (SELECT name FROM Sandbox..desired_sandbox)              
    AND TIMESTAMPDIFF(MONTH,pd_date,NOW()) < 3

Then do something like this assuming the syntax is right:

UPDATE  accounts a -- Is this the correct way to add an alias and update the temp table?                
    JOIN    table_detail d ON a.data_id = d.data_id                 
    JOIN    table_pool p ON d.pp_id = p.pp_id                   
SET     a.flag = 0              
WHERE   d.pd_date < a.pd_date                   
    AND pp_name != 'error';

Finally, I assume I could follow this post to wrap the final query into a stored procedure, correct? To summarize the code in the post:

drop procedure if exists procedure_name;
DELIMITER $$
create procedure procedure_name ()
BEGIN
    DROP TEMPORARY TABLE IF EXISTS accounts;

    CREATE TEMPORARY TABLE accounts AS (
    SELECT...
    FROM...
    WHERE...
    ;
    )

    UPDATE accounts
        JOIN ...
        JOIN ...
    SET...
    WHERE...;
    
    DROP TEMPORARY TABLE accounts; -- otherwise it survives the stored proc call
END
$$ -- signify end of block
DELIMITER ; -- reset to default delimiter

are you using mysql 8.x? then you can use WITH clause, the stored procedure you postet is also good, for your purpose — nbk, Jul 28 '20 at 20:51
thanks, I will have to work on getting the version from the DBA but I would assume not. Thanks for your guidance though glad to hear I am on the right track. — tlk27, Jul 28 '20 at 21:01
What is the point of this procedure? It doesn't make much sense to update a temporary table and not do something else with it later. — Joel Coehoorn, Jul 28 '20 at 21:32
I am using it to make a report, so there is a final query at the end. I wanted to keep the amount of code needed to a minimum for the parts I was unsure of. I should have mentioned that in the beginning. — tlk27, Jul 28 '20 at 21:38

score 0 · Accepted Answer · answered Aug 04 '20 at 21:53

I wanted to post the solution that finally worked for me for others that might be having the same issue. The code below resolves this.

DROP PROCEDURE IF EXISTS procedure_name;

DELIMITER $$

CREATE PROCEDURE procedure_name ()
BEGIN
    DROP TEMPORARY TABLE IF EXISTS accounts;

    CREATE TEMPORARY TABLE accounts AS (
        SELECT  d.data_id                   
               ,p.pp_name AS name                              
               ,1 AS flag             
               ,d.pd_date
               ,DATE_FORMAT(pd_date,'%Y%m%d') AS date_period               
               ,CONVERT(varchar(3),0) AS n_phones                                  
       FROM    table_detail d                  
           JOIN    table_pool p ON d.pp_id = p.pp_id                   
           JOIN    table_type t ON p.pp_type_id = t.pp_type_id                                 
           JOIN    Inventory i ON d.data_id = i.data_id                    
           JOIN    Product pr ON i.product_id = pr.product_id  
       WHERE   pp_name IN (SELECT name FROM Sandbox..desired_sandbox)              
           AND TIMESTAMPDIFF(MONTH,pd_date,NOW()) < 3
    
    );
    
    -- Update the temp table based on specified criteria
    UPDATE accounts
        JOIN ...
        JOIN ...
    SET...
    WHERE...;

    -- Create final Query for report
    SELECT ...
    FROM accounts
    WHERE ...
    GROUP BY ... -- whatever you need for final query
    
    DROP TEMPORARY TABLE accounts; -- otherwise it survives the stored proc call
END
$$ -- signify end of block
DELIMITER ; -- reset to default delimiter

However, it is important to note the limits of MySQL temporary tables. Although I did not indicate it in my original post, later, I was attempting to join the temporary table onto itself. I needed to follow the suggestion outlined here. Essentially, if you need to refer to a temp table with itself you need to make a copy of the temp table: CREATE TEMPORARY TABLE accounts2 AS (SELECT * FROM accounts). Then you can join a temp table to itself.

Converting T-SQL to MySQL with temp tables

1 Answers1