Skip to content
Advertisement

Updating each column on schedule by values from another column

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.

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement