mysql 更新替换字段

更新替换字段中某个值

1. 操作

• 我想把 AU-20230130162944958 替换成 SR-20230130162944958

  
  QQ截图20230131152602.png

• 语句

UPDATE  `TbTaskItem` SET sCode =  CONCAT("SR",REPLACE(sCode,'AU',''))  WHERE pkid=1596

QQ截图20230131152941.png

• 多次更新会出现以下情况

  
  QQ截图20230131163112.png

• 显然不符合要求，修改语句

UPDATE  `TbTaskItem` SET sCode = (CASE WHEN  sCode LIKE '%AU%' THEN CONCAT('SR',REPLACE(sCode,'AU','')) ELSE sCode  END )  WHERE pkid=1596;

QQ截图20230131163255.png

2. 补充

• 根据条件赋值

SELECT nState,CASE  WHEN nState =2 THEN '支付失败' WHEN nState=3 THEN '已支付' ELSE '' END AS SState FROM TbRewardRecord 

QQ截图20230201171159.png