1

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.

  • Why have you written this as procedural code? You can do this in two SQL statements. – Colin 't Hart May 23 '17 at 7:14
  • @Colin'tHart It needs to be done automatically. If this works i would set a trigger to call the store procedure. – CodeIt May 23 '17 at 7:58
2

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;
  • Add BEGIN...COMMIT to make it transactional. Replace DATE_FORMAT(NOW(),'%Y-%m-%d') with simply CURDATE(). – Rick James May 23 '17 at 17:27
  • @RickJames It worked without COMMIT. I replaced date_format with curdate(). Thanks for the suggestion. – CodeIt May 24 '17 at 4:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.