CODEWITHSUNDEEP
mysqltemp-tables
Botem Bao
Botem Bao

Reputation: 817

Backup data into another table

I got a error like "Unknown table 'matched_temp'" when execute the following stored procedure.

DELIMITER $$

DROP PROCEDURE IF EXISTS `apms`.`sp_partition_parkinout` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_partition_parkinout`()
BEGIN

DROP TEMPORARY TABLE IF EXISTS MATCHED_TEMP;

CREATE TEMPORARY TABLE MATCHED_TEMP AS
    SELECT ID FROM CARDINOUT
    WHERE (`State` = 2) AND
        (ID NOT IN (SELECT ID FROM CARDINOUT_MATCHED))
    LIMIT 200;

INSERT INTO CARDINOUT_MATCHED
SELECT * FROM CARDINOUT
WHERE ID IN (SELECT ID FROM MATCHED_TEMP);

DELETE FROM CARDINOUT
WHERE ID IN (SELECT ID FROM MATCHED_TEMP);

END $$

DELIMITER ;

I want to execute it periodically to move data from cardinout into cardinout_matched. The purpose to keep table cardinout as small as possible because cardinout is queried quite often.

Is there any other option to accomplish this?

Thanks Botem

Upvotes: 1

Views: 137

Answers (1)

bfavaretto
bfavaretto

Reputation: 71939

CREATE TEMPORARY TABLE MATCHED_TEMP (
    ID INT
);
INSERT INTO MATCHED_TEMP
SELECT ID FROM CARDINOUT 
(...)

Upvotes: 1

Related Questions