title: ERROR 1137: Can't reopen table when joining a temporary table
body: <p>I have a stored procedure:</p>
  
  <pre><code>DROP PROCEDURE IF EXISTS dijResolve; 
  DELIMITER | 
  CREATE PROCEDURE dijResolve( pFromNodeName VARCHAR(20), pToNodeName VARCHAR(20) ) 
  BEGIN 
    DECLARE vFromNodeID, vToNodeID, vNodeID, vCost, vPathID INT; 
    CREATE TEMPORARY TABLE new_dijnodes engine=memory AS SELECT * FROM dijnodes;
    CREATE TEMPORARY TABLE new_dijpaths AS SELECT * FROM dijpaths;
  
    -- null out path info in the nodes table 
    UPDATE new_dijnodes SET PathID = NULL,Cost = NULL,Calculated = 0; 
    -- find nodeIDs referenced by input params 
    SET vFromNodeID = ( SELECT NodeID FROM new_dijnodes WHERE NodeName = pFromNodeName ); 
    IF vFromNodeID IS NULL THEN 
      SELECT CONCAT('From node name ', pFromNodeName, ' not found.' );  
    ELSE 
      BEGIN 
        -- start at src node 
        SET vNodeID = vFromNodeID; 
        SET vToNodeID = ( SELECT NodeID FROM new_dijnodes WHERE NodeName = pToNodeName ); 
        IF vToNodeID IS NULL THEN 
          SELECT CONCAT('From node name ', pToNodeName, ' not found.' ); 
        ELSE 
          BEGIN 
            -- calculate path costs till all are done 
            UPDATE new_dijnodes SET Cost=0 WHERE NodeID = vFromNodeID; 
            WHILE vNodeID IS NOT NULL DO 
              BEGIN 
                UPDATE  
                  new_dijnodes AS src 
                  JOIN new_dijpaths AS paths ON paths.FromNodeID = src.NodeID 
                  JOIN new_dijnodes AS dest ON dest.NodeID = Paths.ToNodeID 
                SET dest.Cost = CASE 
                                  WHEN dest.Cost IS NULL THEN src.Cost + Paths.Cost 
                                  WHEN src.Cost + Paths.Cost &lt; dest.Cost THEN src.Cost + Paths.Cost 
                                  ELSE dest.Cost 
                                END, 
                    dest.PathID = Paths.PathID 
                WHERE  
                  src.NodeID = vNodeID 
                  AND (dest.Cost IS NULL OR src.Cost + Paths.Cost &lt; dest.Cost) 
                  AND dest.Calculated = 0; 
  
                UPDATE new_dijnodes SET Calculated = 1 WHERE NodeID = vNodeID; 
  
                SET vNodeID = ( SELECT nodeID FROM new_dijnodes 
                                WHERE Calculated = 0 AND Cost IS NOT NULL 
                                ORDER BY Cost LIMIT 1 
                              ); 
              END; 
            END WHILE; 
          END; 
        END IF; 
      END; 
    END IF; 
    IF EXISTS( SELECT 1 FROM new_dijnodes WHERE NodeID = vToNodeID AND Cost IS NULL ) THEN 
      -- problem,  cannot proceed 
      SELECT CONCAT( 'Node ',vNodeID, ' missed.' ); 
    ELSE 
      BEGIN 
        -- write itinerary to map table 
        DROP TEMPORARY TABLE IF EXISTS map; 
        CREATE TEMPORARY TABLE map ( 
          RowID INT PRIMARY KEY AUTO_INCREMENT, 
          FromNodeName VARCHAR(20), 
          ToNodeName VARCHAR(20), 
          Cost INT 
        ) ENGINE=MEMORY; 
        WHILE vFromNodeID &lt;&gt; vToNodeID DO 
          BEGIN 
            SELECT  
              src.NodeName,dest.NodeName,dest.Cost,dest.PathID 
              INTO vFromNodeName, vToNodeName, vCost, vPathID 
            FROM  
              new_dijnodes AS dest 
              JOIN new_dijpaths AS Paths ON Paths.PathID = dest.PathID 
              JOIN new_dijnodes AS src ON src.NodeID = Paths.FromNodeID 
            WHERE dest.NodeID = vToNodeID; 
  
            INSERT INTO Map(FromNodeName,ToNodeName,Cost) VALUES(vFromNodeName,vToNodeName,vCost); 
  
            SET vToNodeID = (SELECT FromNodeID FROM new_dijpaths WHERE PathID = vPathID); 
          END; 
        END WHILE; 
        SELECT FromNodeName,ToNodeName,Cost FROM Map ORDER BY RowID DESC; 
        DROP TEMPORARY TABLE Map; 
      END; 
    END IF; 
  END; 
  | 
  DELIMITER ;
  </code></pre>
  
  <p>This function is taken from this site
  <a href="http://www.artfulsoftware.com/infotree/qrytip.php?id=766" rel="nofollow noreferrer">http://www.artfulsoftware.com/infotree/qrytip.php?id=766</a></p>
  
  <p>well I have changed it to be able to do calculations on temporary tables so it is not any need to save data in tables. But I faced a problem, Mysql does not let to call a temporary table with other names. so In above code I'll face an error which says</p>
  
  <pre><code>#1137 - Can't reopen table: 'src'
  </code></pre>
  
  <p>the above error comes from this query</p>
  
  <pre><code>      UPDATE  
          new_dijnodes AS src 
          JOIN new_dijpaths AS paths ON paths.FromNodeID = src.NodeID 
          JOIN new_dijnodes AS dest ON dest.NodeID = Paths.ToNodeID 
        SET dest.Cost = CASE 
                          WHEN dest.Cost IS NULL THEN src.Cost + Paths.Cost 
                          WHEN src.Cost + Paths.Cost &lt; dest.Cost THEN src.Cost + Paths.Cost 
                          ELSE dest.Cost 
                        END, 
            dest.PathID = Paths.PathID 
        WHERE  
          src.NodeID = vNodeID 
          AND (dest.Cost IS NULL OR src.Cost + Paths.Cost &lt; dest.Cost) 
          AND dest.Calculated = 0; 
  </code></pre>
  
  <p>here is <a href="http://sqlfiddle.com/#!9/bc5a01c" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/bc5a01c</a> data of my tables
  As you can see the above query joins the same table once as <code>src</code> and again as <code>dest</code> and updates their fields. I tried to create another  <code>new_dijnodes</code> but I could not make it work,Here is my try</p>
  
  <pre><code>DROP PROCEDURE IF EXISTS dijResolve; 
  DELIMITER | 
  CREATE PROCEDURE dijResolve( pFromNodeName VARCHAR(20), pToNodeName VARCHAR(20) ) 
  BEGIN 
    DECLARE vFromNodeID, vToNodeID, vNodeID, vCost, vPathID INT; 
    DECLARE vFromNodeName, vToNodeName VARCHAR(20); 
    DROP TEMPORARY TABLE IF EXISTS new_dijnodes; 
    DROP TEMPORARY TABLE IF EXISTS new_dijpaths; 
  
    CREATE TEMPORARY TABLE new_dijnodes engine=memory AS SELECT * FROM dijnodes;
    CREATE TEMPORARY TABLE new_dijpaths AS SELECT * FROM dijpaths;
  
    -- null out path info in the nodes table 
    UPDATE new_dijnodes SET PathID = NULL,Cost = NULL,Calculated = 0; 
    -- find nodeIDs referenced by input params 
    SET vFromNodeID = ( SELECT NodeID FROM new_dijnodes WHERE NodeName = pFromNodeName ); 
    IF vFromNodeID IS NULL THEN 
      SELECT CONCAT('From node name ', pFromNodeName, ' not found.' );  
    ELSE 
      BEGIN 
        -- start at src node 
        SET vNodeID = vFromNodeID; 
        SET vToNodeID = ( SELECT NodeID FROM new_dijnodes WHERE NodeName = pToNodeName ); 
        IF vToNodeID IS NULL THEN 
          SELECT CONCAT('From node name ', pToNodeName, ' not found.' ); 
        ELSE 
          BEGIN 
            -- calculate path costs till all are done 
            UPDATE new_dijnodes SET Cost=0 WHERE NodeID = vFromNodeID; 
            WHILE vNodeID IS NOT NULL DO 
              BEGIN 
              DROP TEMPORARY TABLE IF EXISTS new_dijnodes_dst; 
              CREATE TEMPORARY TABLE new_dijnodes_dst AS SELECT * FROM new_dijnodes;
  
                UPDATE  
                  new_dijnodes  
                  JOIN new_dijpaths  ON new_dijpaths.FromNodeID = new_dijnodes.NodeID 
                  JOIN new_dijnodes_dst ON new_dijnodes_dst.NodeID = new_dijpaths.ToNodeID 
                SET new_dijnodes_dst.Cost = CASE 
                                  WHEN new_dijnodes_dst.Cost IS NULL THEN new_dijnodes.Cost + new_dijpaths.Cost 
                                  WHEN new_dijnodes.Cost + new_dijpaths.Cost &lt; new_dijnodes_dst.Cost THEN new_dijnodes.Cost + new_dijpaths.Cost 
                                  ELSE new_dijnodes_dst.Cost 
                                END, 
                    new_dijnodes_dst.PathID = new_dijpaths.PathID 
                WHERE  
                  new_dijnodes.NodeID = vNodeID 
                  AND (new_dijnodes_dst.Cost IS NULL OR new_dijnodes.Cost + new_dijpaths.Cost &lt; new_dijnodes_dst.Cost) 
                  AND new_dijnodes_dst.Calculated = 0; 
  
                UPDATE new_dijnodes SET Calculated = 1 WHERE NodeID = vNodeID; 
  
                SET vNodeID = ( SELECT nodeID FROM new_dijnodes 
                                WHERE Calculated = 0 AND Cost IS NOT NULL 
                                ORDER BY Cost LIMIT 1 
                              ); 
              END; 
            END WHILE; 
          END; 
        END IF; 
      END; 
    END IF; 
    IF EXISTS( SELECT 1 FROM new_dijnodes WHERE NodeID = vToNodeID AND Cost IS NULL ) THEN 
      -- problem,  cannot proceed 
      SELECT CONCAT( 'Node ',vNodeID, ' missed.' ); 
    ELSE 
      BEGIN 
        -- write itinerary to map table 
        DROP TEMPORARY TABLE IF EXISTS map; 
        CREATE TEMPORARY TABLE map ( 
          RowID INT PRIMARY KEY AUTO_INCREMENT, 
          FromNodeName VARCHAR(20), 
          ToNodeName VARCHAR(20), 
          Cost INT 
        ) ENGINE=MEMORY; 
        WHILE vFromNodeID &lt;&gt; vToNodeID DO 
          BEGIN 
          DROP TEMPORARY TABLE IF EXISTS new_dijnodes_src; 
          CREATE TEMPORARY TABLE new_dijnodes_src AS SELECT * FROM new_dijnodes;
  
            SELECT  
              new_dijnodes_src.NodeName,new_dijnodes.NodeName,new_dijnodes.Cost,new_dijnodes.PathID 
              INTO vFromNodeName, vToNodeName, vCost, vPathID 
            FROM  
              new_dijnodes 
              JOIN new_dijpaths  ON new_dijpaths.PathID = new_dijnodes.PathID 
              JOIN new_dijnodes_src  ON new_dijnodes_src.NodeID = new_dijpaths.FromNodeID 
            WHERE new_dijnodes.NodeID = vToNodeID; 
  
            INSERT INTO Map(FromNodeName,ToNodeName,Cost) VALUES(vFromNodeName,vToNodeName,vCost); 
  
            SET vToNodeID = (SELECT FromNodeID FROM new_dijpaths WHERE PathID = vPathID); 
          END; 
        END WHILE; 
        SELECT FromNodeName,ToNodeName,Cost FROM Map ORDER BY RowID DESC; 
        DROP TEMPORARY TABLE Map; 
      END; 
    END IF; 
  END; 
  | 
  DELIMITER ;
  </code></pre>
  
  <p>but it always returns  <code>SELECT CONCAT( 'Node ',vNodeID, ' missed.' );</code>  as It updates <code>new_dijnodes_dst</code> which is tables duplication.</p>
  
  <p>I also can not make any real table as this procedure is unique for each user and its handling is not easy for multiuser. 
  what solutions are available to fix this problem?
  thanks </p>