CODEWITHSUNDEEP
mysqlmariadb
sanjihan
sanjihan

Reputation: 6046

update record value based on some other value in the same record

I have the following table in my database:

 CREATE TABLE `plans` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `user_id` int(11) DEFAULT NULL,
      `plan` varchar(64) DEFAULT NULL,
      `subscr_id` varchar(64) DEFAULT NULL,
      `last_payment` timestamp NULL DEFAULT NULL,
      `active` tinyint(1) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=latin1

Plan values are 1 MONTH, 3 MONTHS and YEAR corresponding to 1 month, 3 months and 12 months.

Record example:

id----user_id-----plan--------subcr_id-------last_payment--------active

30 |    15     |    1 MONTH  |  A-1   |   2017-05-19 08:22:29   |1

I would like to go thorugh every record and update active to 0 if last_payment happened over a month ago (if the plan for that record is 1M), over 3 months ago (if the plan for that record is 3 MONTHS) and over 12 months ago (if the plan for that record is YEAR).

While I am comfortable with simple update statements, this one requires some if logic I don't know how to perform. Can you help me out?

Upvotes: 1

Views: 23

Answers (2)

cn0047
cn0047

Reputation: 17091

update plans set active = case
when plan = '1 MONTH' and last_payment < now() - interval 1 month then 0
when plan = '3 MONTH' and last_payment < now() - interval 3 month then 0
when plan = 'YEAR' and last_payment < now() - interval 1 year then 0
else 1
end;

Upvotes: 1

Ricardo Ortega Maga&#241;a
Ricardo Ortega Maga&#241;a

Reputation: 2442

I think you should try something like this:

UPDATE plans t, (SELECT *  FROM plans) t1
   SET t.active = 0 WHERE date(now()) not between date_add(t1.last_payment,interval t1.plan month)

Just need to change the plan field to an integer field, or interval (date), should be easier than making a Case scenario, but you can do it too:

UPDATE `plan` SET `active` = CASE
    WHEN plan = '1 MONTH' THEN CAST(not between date_add(last_payment, interval 1 MONTH) AS SIGNED INTEGER);
    WHEN plan = '3 MONTHS' THEN CAST(not between date_add(last_payment, interval 3 MONTH) AS SIGNED INTEGER);
    WHEN plan = '1 YEAR' THEN CAST(not between date_add(last_payment, interval 1 YEAR) AS SIGNED INTEGER);
    ELSE `1`
    END

Something like that, hope it helps you

Upvotes: 1

Related Questions