连表更新 计算 类型转换

-- 目标u_order_goods表字段 cinvname cinvname cinvm_unit iquantity 值为u_goods_support中的字段值 条件是u_order_goods.iquantity > u_goods_support.support_exchange
--  关闭自动提交 先测试数据处理的准确定再执行commit
SET autocommit = 0;

--  查询出单条数据做数据验证
SELECT
	og.*,
	o.spare_field2
FROM
	u_order_goods AS og
	LEFT JOIN u_order AS o ON o.id = og.order_id 
WHERE
	o.spare_field2 = 0 and og.id=206145 order by og.iquantity asc;


-- 连表更新
UPDATE u_order_goods AS og
LEFT JOIN u_order AS o ON og.order_id = o.id
LEFT JOIN u_goods_support AS gs ON gs.prod_id = og.cinvcode 
-- 字段赋值采用条件判断og.iquantity >= gs.support_exchange 此处不用关心类型是否相同

SET og.iquantity = (
CASE
	WHEN og.iquantity >= gs.support_exchange THEN
	cast(
	cast(
	-- 计算除法时先转换成DECIMAL 然后算完的值转换后面小数点位数 最后转到char类型
	cast( og.iquantity AS DECIMAL ( 18, 1 ) ) / cast( gs.support_exchange AS DECIMAL ( 18, 1 ) ) AS DECIMAL ( 18, 0 ) 
	) AS CHAR 
	) ELSE og.iquantity 
END 
	),
	og.cinvname = ( CASE WHEN og.iquantity >= gs.support_exchange THEN gs.prod_name ELSE og.cinvname END ),
	og.cinvm_unit = ( CASE WHEN og.iquantity >= gs.support_exchange THEN gs.support_unit ELSE og.cinvm_unit END ) 
WHERE
-- 此处一定要注意条件的类型 varchar要加'
	o.spare_field2 = '0' 
	AND gs.support_unit = '件';
	
--  再次验证查出的数据是否是想要的结果
SELECT
	og.*,
	o.spare_field2
FROM
	u_order_goods AS og
	LEFT JOIN u_order AS o ON o.id = og.order_id 
WHERE
	o.spare_field2 = 0 and og.id=206145 order by og.iquantity asc;
	
	
	
	
-- 验证失败进行回滚
ROLLBACK;
-- 验证成功进行提交
COMMIT;