Johny Santiago Johny Santiago - 1 year ago 50
MySQL Question

Periodically Delete MySQL Table A Rows but keep the associated table B data which gets updated based on as per data of table A

I have two Tables Table A and Table B.. Table A stores data on every page load where Table B get's updated based on the data of Table A. Now, i want to delete Table A rows after specific time period but keep the data in Table B as it is which becomes zero after the removal of Table A rows.

My Table A have UserIP column where Table B gets updated by the count of Unique IP addresses and count of Rows in table A. Let's say there are 10 Unique IP and 120 Rows in Table A so Table B will show the count 120 and 10 in it's column fields. Now I want to delete table A rows after specified time but keep the data of Table B fields as they were before deletion that is 120 and 10 and continue the count as per previous data like new row in table A will be counted and 121 in Table B and New IP address will be counted as 11 in Table B.

Any suggestions OR help will be appreciated..

Answer Source

Well, after searching here and there i found that my problem can be solved using MySQL’s Event Scheduler. Following article helped me to get right answer and code.. Hope it can help others as well..