SQL批量修改商品库存

不通过事务可以批量差异化修改多条记录的SQL语句

UPDATE studet 
SET 
    age1 = CASE id
        WHEN 1047 THEN age1 + 1047
        WHEN 1048 THEN age1 + 1048
        WHEN 1049 THEN age1 + 1049
        WHEN 1050 THEN age1 + 1050
    END,
        age2 = CASE
        WHEN id = 1047 THEN age2 + 1047
        WHEN id = 1048 THEN age2 + 1048
        WHEN id = 1049 THEN age2 + 1049
        WHEN id = 1050 THEN age2 + 1050
    END,
WHERE
id
IN
(
1046
1047
1048
1049
1050
)

GO执行记录

func (l *ApiXSBKAIPIAO06ToExamineLogic) ApiXSBKAIPIAO06ToExamine(req *types.ApiKAIPIAOExamineReq) (resp *types.ApiKAIPIAOExamineResp, err error) {
	// 首先查询库存是否满足
	mxRes, err1 := l.svcCtx.KAIPIAOMXModel.FindAllByMap(l.ctx, squirrel.Eq{"fUBIANHAO": req.Num}, "")
	if err1 != nil {
		return nil, err1
	}
	var mxSps []string
	for _, kaipiaomx := range mxRes {
		mxSps = append(mxSps, kaipiaomx.SPBIANHAO)
	}
	spRes, err2 := l.svcCtx.SPModel.FindAllByMap(l.ctx, squirrel.Eq{"BIANHAO": mxSps}, "")
	if err2 != nil {
		return nil, err2
	}
	for _, mx := range mxRes {
		for _, sp := range spRes {
			if mx.SPBIANHAO == sp.BIANHAO.String {
				if sp.XNKC.Int64 < gconv.Int64(mx.SHU) {
					msg := fmt.Sprintf("商品%s库存不足所以审核失败,库存有%v个,开票数%v个,所以库存不足", sp.NAME.String, sp.XNKC.Int64, mx.SHU)
					return &types.ApiKAIPIAOExamineResp{Msg: msg, IsSuccess: false}, nil
				}
			}
		}
	}
	// 到这里说明库存充足开始修改库存余量
	var thenStr string
	var BhArrStr string
	var KPJINE float64
	for i, mx := range mxRes {
		if i == 0 {
			thenStr = fmt.Sprintf("WHEN \"%s\" THEN XNKC - %s ", mx.SPBIANHAO, gconv.String(mx.SHU))
			BhArrStr = fmt.Sprintf("\"%s\"", mx.SPBIANHAO)
			KPJINE = mx.DJ * mx.SHU
		} else {
			thenStr = thenStr + fmt.Sprintf("WHEN \"%s\" THEN XNKC - %s ", mx.SPBIANHAO, gconv.String(mx.SHU))
			BhArrStr = BhArrStr + "," + fmt.Sprintf("\"%s\"", mx.SPBIANHAO)
			KPJINE = KPJINE + mx.DJ*mx.SHU
		}
	}
	sql := fmt.Sprintf("UPDATE SP SET XNKC = CASE BIANHAO %s END WHERE BIANHAO IN ( %s )", thenStr, BhArrStr)
	// 开启事务
	err3 := l.svcCtx.KAIPIAOMXModel.Trans(l.ctx, func(context context.Context, session sqlx.Session) error {
		err4 := l.svcCtx.KAIPIAOModel.UpdateByMap(context, session, squirrel.Eq{"KCZT": "1", "KPJINE": KPJINE}, squirrel.Eq{"BIANHAO": req.Num})
		if err4 != nil {
			return err4
		}
		_, err5 := l.svcCtx.SPModel.SQL(context, nil, sql)
		if err5 != nil {
			return err5
		}
		return nil
	})
	if err3 != nil {
		return nil, err3
	}
	return &types.ApiKAIPIAOExamineResp{Msg: "审核成功", IsSuccess: true}, nil
}