按照其他列中的值按计划更新每列

Question

I have a users table and two columns are having a relationship. The value of the first column is the monthly deductions of each user.

I want the data in the first column to keep auto increasing itself in the other column on a monthly basis. Example: if user A has $5 as their monthly deduction and has $10 as their total_deduction in February, by march the total deduction should auto increase to $15.

I could use event in phpmyadmin sql I guess.

Answer 1

Absolutely, you can schedule an event for monthly deduction. Before creating an event make sure in your mysql you set event_scheduler is ON which in default is OFF.

SET GLOBAL event_scheduler = ON;

Event: you can add fixed amount or user-wise deduction from another column.

1) For fixed amount:

CREATE EVENT user_monthly_deduction
ON SCHEDULE EVERY '1' MONTH
DO
BEGIN
  UPDATE test.users SET total_deductions = total_deductions + $5;
END$$

2) From another column:

CREATE EVENT user_monthly_deduction
ON SCHEDULE EVERY '1' MONTH
DO
BEGIN
  UPDATE test.users SET total_deductions = total_deductions + monthly_deduction;
END$$

For more details about events-configuration and create_event you can check this official document. mysql-event-configuration and create-event