I have got two tables i_mast and exp_mast. The structure of both tables are same.
+-----------------+ |id INT(5) | |name VARCHAR(30) | |e_date DATE | +-----------------+
When the e_date matches current date i need to insert those rows to exp_mast and then delete those inserted rows from i_mast. I wrote the below stored procedure for this:
CREATE PROCEDURE is_expired() BEGIN DECLARE id INT(5); DECLARE name VARCHAR(30); DECLARE e_date DATE; DECLARE n INT; DECLARE i INT DEFAULT 0; SET n = (SELECT COUNT(*) AS num FROM i_mast WHERE e_date = DATE_FORMAT(NOW(), CURDATE()); WHILE i < n DO SELECT id FROM i_mast WHERE e_date = CURDATE() LIMIT 1 INTO id; SELECT name FROM i_mast WHERE e_date = CURDATE() LIMIT 1 INTO name; SELECT e_date FROM i_mast WHERE e_date = CURDATE() LIMIT 1 INTO e_date; INSERT INTO exp_mast VALUES(id,name,e_date); DELETE from i_mast WHERE e_date = CURDATE() LIMIT 1; SET i = i + 1; END WHILE; END;
It help me create routine named is_expired(). I can see it under routines tab in phpmyadmin. After that i tried executing and it gave me this result
0 rows affected by the last statement inside the procedure.. I have got two records that needs to be deleted today from i_mast, while making a copy in exp_mast. But it's not having any effect. Please help me solve this issue.