MySQL – Fixing ‘0 Rows Affected’ in Stored Procedure

MySQLphpmyadminstored-procedures

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.

Best Answer

Something like the following should work for you:

CREATE PROCEDURE is_expired()
BEGIN
  INSERT INTO exp_mast(id,name,e_date)
    SELECT id, name, e_date
    FROM i_mast
    WHERE e_date = CURDATE();

  DELETE from i_mast
  WHERE e_date = CURDATE();
END;
Related Question