mysql if then else update procedure

i want upgrade statement in procedure:

我想在程序中升级语句:

DROP PROCEDURE IF EXISTS `update` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update`
     (IN mobile VARCHAR(20), IN registered BOOLEAN, IN charge BOOLEAN, IN debit_mon INT)
BEGIN
 DECLARE chargeTime Datetime;
 DECLARE lastdayNow Integer;
 DECLARE lastdayNext Integer;

 SET lastdayNow =  DAY(now());
 SET lastdayNext =  DAY(LAST_DAY(now() + interval 1 month));

 IF lastdayNow > lastdayNext then
   SET chargeTime = LAST_DAY(now() + interval 1 month);
 ELSE
   SET chargeTime = now() + interval 1 month;
 END IF;

 UPDATE `chat_acc` SET
     `registered` = registered,
     `charge` = charge,
     `debit_mon` = debit_mon,
     CHARGE_TIME = chargeTime 
   WHERE `phone` =mobile;
END $$

i want add case check field source:

我想添加案例检查字段来源:

if  source = 'A' or source = 'B' then set Charge Time = now() + interval 1 week

Thank any your help

谢谢你的帮助

2 个解决方案

#1

Assuming the field name is source. Try to add this in your stored procedures:

假设字段名称是source。尝试在存储过程中添加:

IF [source] = 'A' OR [source] = 'B' then
  SET chargeTime = now() + interval 1 week
END IF;

#2

Just guessing here, but if source is a column in chat_acc you can do something like this in your UPDATE statement:

只是在这里猜测,但如果source是chat_acc中的一列,你可以在UPDATE语句中执行以下操作:

UPDATE `chat_acc` SET
   `registered` = registered,
   `charge` = charge,
   `debit_mon` = debit_mon,
   CHARGE_TIME =
      CASE WHEN chat_acc.source IN ('A', 'B') THEN now() + interval 1 week
      ELSE chargeTime
      END
WHERE `phone` =mobile;

#1