-2

I want to decrease the number of days by 1 daily. And update the remaining days in the same row and column. Below you can see my MySQL table : users table

In the above image, you can see that I have a column "licence_validity" that contains the number of days and you can also see I have another column "activated_on" now this column will be filled when licence get activated with the current date. Now I want to decrease the number of days by 1 daily in the licence_validity column when activated_on column has a date.

Shadow
  • 33,525
  • 10
  • 51
  • 64
DCodeMania
  • 1,027
  • 2
  • 7
  • 19

1 Answers1

1

Now I want to decrease the number of days by 1 daily in the licence_validity column when activated_on column has a date.

I am understanding this correctly, that's a simple update:

update users
set license_validity = license_validity - 1
where activated_on is not null

If you want to do this on a daily basis, then you might want to use the event scheduler. Something like:

create event event_update_users
on schedule every 1 day
do
    update users
    set license_validity = license_validity - 1
    where activated_on is not null;
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Can you please show me how can I do this by event scheduler? @GMB – DCodeMania Oct 23 '20 at 11:55
  • @DCodeMania: I don't understand your question. This is a SQL statement, that you do run in your sql client (php my admin, if that's what you are using). – GMB Oct 23 '20 at 12:04
  • in my case it decreased my date by 1 second, my column type is `datetime` – moghwan May 31 '22 at 14:32