某个款号的颜色错了，也有单据了
<p>日期：2018-7-13
提问人：黄兵</p>
<p>问题：CX031150N-05这个款号的颜色错了（302--96；204--53），错误的颜色有单据、库存（店铺已经有零售了）。他们是想把错误的色号都改为正确的，然后把错误的色号删除</p>
<p>语句刷新：
--删除商品相关表
update g_spdm set c_huoj='02,11,32,53,67,81,91,93,96',d_upid=now() where code='CX031150N-05';
delete from g_spdmdetail where code='CX031150N-05' and colorid in ('302','204');
delete from g_spdmdtl where code='CX031150N-05' and colorid in ('302','204');
delete from g_barcodedetail where c_code='CX031150N-05' and c_colorid in ('302','204');</p>
<p>--库存表
--合并已存在53色号的商品数量
update g_endstore set amount=coalesce(g_endstore.amount,0)+coalesce(b.amount,0)
from (
select shopid,code,xsize,amount from g_endstore where code='CX031150N-05' and colorid='204'
) b
where g_endstore.shopid=b.shopid and g_endstore.code=b.code and g_endstore.xsize=b.xsize
and g_endstore.code='CX031150N-05' and g_endstore.colorid='53';</p>
<p>--删除204色号
update g_endstore set c_id='del'
from (
select shopid,code,xsize,amount from g_endstore where code='CX031150N-05' and colorid='53'
) b
where g_endstore.shopid=b.shopid and g_endstore.code=b.code and g_endstore.xsize=b.xsize
and g_endstore.code='CX031150N-05' and g_endstore.colorid='204';
delete from g_endstore where code='CX031150N-05' and colorid='204' and c_id='del';</p>
<p>--更新204色号为53
update g_endstore set colorid='53' where code='CX031150N-05' and colorid='204';</p>
<p>--配货表
--合并已存在53色号的商品数量
update g_endstoreph set amount=coalesce(g_endstoreph.amount,0)+coalesce(b.amount,0)
from (
select shopid,code,xsize,amount from g_endstoreph where code='CX031150N-05' and colorid='204'
) b
where g_endstoreph.shopid=b.shopid and g_endstoreph.code=b.code and g_endstoreph.xsize=b.xsize
and g_endstoreph.code='CX031150N-05' and g_endstoreph.colorid='53';</p>
<p>--删除204色号
update g_endstoreph set c_id='del'
from (
select shopid,code,xsize,amount from g_endstoreph where code='CX031150N-05' and colorid='53'
) b
where g_endstoreph.shopid=b.shopid and g_endstoreph.code=b.code and g_endstoreph.xsize=b.xsize
and g_endstoreph.code='CX031150N-05' and g_endstoreph.colorid='204';
delete from g_endstoreph where code='CX031150N-05' and colorid='204' and c_id='del';</p>
<p>--更新204色号为53
update g_endstoreph set colorid='53' where code='CX031150N-05' and colorid='204';</p>
<p>--期货表
--合并已存在53色号的商品数量
update g_endstoreqh set amount=coalesce(g_endstoreqh.amount,0)+coalesce(b.amount,0)
from (
select shopid,code,xsize,amount from g_endstoreqh where code='CX031150N-05' and colorid='204'
) b
where g_endstoreqh.shopid=b.shopid and g_endstoreqh.code=b.code and g_endstoreqh.xsize=b.xsize
and g_endstoreqh.code='CX031150N-05' and g_endstoreqh.colorid='53';</p>
<p>--删除204色号
update g_endstoreqh set c_upztm='del'
from (
select shopid,code,xsize,amount from g_endstoreqh where code='CX031150N-05' and colorid='53'
) b
where g_endstoreqh.shopid=b.shopid and g_endstoreqh.code=b.code and g_endstoreqh.xsize=b.xsize
and g_endstoreqh.code='CX031150N-05' and g_endstoreqh.colorid='204';
delete from g_endstoreqh where code='CX031150N-05' and colorid='204' and c_upztm='del';</p>
<p>--更新204色号为53
update g_endstoreqh set colorid='53' where code='CX031150N-05' and colorid='204';</p>
<p>--采购汇总表
--合并已存在53色号的商品数量
update g_endstorecgex set amount=coalesce(g_endstorecgex.amount,0)+coalesce(b.amount,0)
from (
select shopid,code,xsize,amount from g_endstorecgex where code='CX031150N-05' and colorid='204'
) b
where g_endstorecgex.shopid=b.shopid and g_endstorecgex.code=b.code and g_endstorecgex.xsize=b.xsize
and g_endstorecgex.code='CX031150N-05' and g_endstorecgex.colorid='53';</p>
<p>--删除204色号
update g_endstorecgex set c_upztm='del'
from (
select shopid,code,xsize,amount from g_endstorecgex where code='CX031150N-05' and colorid='53'
) b
where g_endstorecgex.shopid=b.shopid and g_endstorecgex.code=b.code and g_endstorecgex.xsize=b.xsize
and g_endstorecgex.code='CX031150N-05' and g_endstorecgex.colorid='204';
delete from g_endstorecgex where code='CX031150N-05' and colorid='204' and c_upztm='del';</p>
<p>--更新204色号为53
update g_endstorecgex set colorid='53' where code='CX031150N-05' and colorid='204';</p>
<p>--进销存单据表
--合并已存在53色号的商品数量
update g_iodetail set amount=coalesce(g_iodetail.amount,0)+coalesce(b.amount,0),sale=(coalesce(g_iodetail.amount,0)+coalesce(b.amount,0))*g_iodetail.price
from (
select scripno,code,xsize,amount from g_iodetail where code='CX031150N-05' and colorid='204'
) b
where g_iodetail.scripno=b.scripno and g_iodetail.code=b.code and g_iodetail.xsize=b.xsize
and g_iodetail.code='CX031150N-05' and g_iodetail.colorid='53';</p>
<p>--删除204色号
update g_iodetail set c_beiz='del'
from (
select scripno,code,xsize,amount from g_iodetail where code='CX031150N-05' and colorid='53'
) b
where g_iodetail.scripno=b.scripno and g_iodetail.code=b.code and g_iodetail.xsize=b.xsize
and g_iodetail.code='CX031150N-05' and g_iodetail.colorid='204';
delete from g_iodetail where code='CX031150N-05' and colorid='204' and c_beiz='del';</p>
<p>--更新204色号为53
update g_iodetail set colorid='53' where code='CX031150N-05' and colorid='204';</p>
<p>--非进销存单据表
--合并已存在53色号的商品数量
update g_iodetailbill set amount=coalesce(g_iodetailbill.amount,0)+coalesce(b.amount,0),sale=(coalesce(g_iodetailbill.amount,0)+coalesce(b.amount,0))*g_iodetailbill.price
from (
select scripno,code,xsize,amount from g_iodetailbill where code='CX031150N-05' and colorid='204'
) b
where g_iodetailbill.scripno=b.scripno and g_iodetailbill.code=b.code and g_iodetailbill.xsize=b.xsize
and g_iodetailbill.code='CX031150N-05' and g_iodetailbill.colorid='53';</p>
<p>--删除204色号
update g_iodetailbill set c_beiz='del'
from (
select scripno,code,xsize,amount from g_iodetailbill where code='CX031150N-05' and colorid='53'
) b
where g_iodetailbill.scripno=b.scripno and g_iodetailbill.code=b.code and g_iodetailbill.xsize=b.xsize
and g_iodetailbill.code='CX031150N-05' and g_iodetailbill.colorid='204';
delete from g_iodetailbill where code='CX031150N-05' and colorid='204' and c_beiz='del';</p>
<p>--更新204色号为53
update g_iodetailbill set colorid='53' where code='CX031150N-05' and colorid='204';</p>
<p>--零售小票表
--合并已存在53色号的商品数量
update g_iodetail_zmd set amount=coalesce(g_iodetail_zmd.amount,0)+coalesce(b.amount,0),sale=(coalesce(g_iodetail_zmd.amount,0)+coalesce(b.amount,0))*g_iodetail_zmd.price
from (
select scripno,code,xsize,amount from g_iodetail_zmd where code='CX031150N-05' and colorid='204'
) b
where g_iodetail_zmd.scripno=b.scripno and g_iodetail_zmd.code=b.code and g_iodetail_zmd.xsize=b.xsize
and g_iodetail_zmd.code='CX031150N-05' and g_iodetail_zmd.colorid='53';</p>
<p>--删除204色号
update g_iodetail_zmd set c_beiz='del'
from (
select scripno,code,xsize,amount from g_iodetail_zmd where code='CX031150N-05' and colorid='53'
) b
where g_iodetail_zmd.scripno=b.scripno and g_iodetail_zmd.code=b.code and g_iodetail_zmd.xsize=b.xsize
and g_iodetail_zmd.code='CX031150N-05' and g_iodetail_zmd.colorid='204';
delete from g_iodetail_zmd where code='CX031150N-05' and colorid='204' and c_beiz='del';</p>
<p>--更新204色号为53
update g_iodetail_zmd set colorid='53' where code='CX031150N-05' and colorid='204';</p>
<p>--库存表
--合并已存在96色号的商品数量
update g_endstore set amount=coalesce(g_endstore.amount,0)+coalesce(b.amount,0)
from (
select shopid,code,xsize,amount from g_endstore where code='CX031150N-05' and colorid='302'
) b
where g_endstore.shopid=b.shopid and g_endstore.code=b.code and g_endstore.xsize=b.xsize
and g_endstore.code='CX031150N-05' and g_endstore.colorid='96';</p>
<p>--删除302色号
update g_endstore set c_id='del'
from (
select shopid,code,xsize,amount from g_endstore where code='CX031150N-05' and colorid='96'
) b
where g_endstore.shopid=b.shopid and g_endstore.code=b.code and g_endstore.xsize=b.xsize
and g_endstore.code='CX031150N-05' and g_endstore.colorid='302';
delete from g_endstore where code='CX031150N-05' and colorid='302' and c_id='del';</p>
<p>--更新302色号为96
update g_endstore set colorid='96' where code='CX031150N-05' and colorid='302';</p>
<p>--配货表
--合并已存在96色号的商品数量
update g_endstoreph set amount=coalesce(g_endstoreph.amount,0)+coalesce(b.amount,0)
from (
select shopid,code,xsize,amount from g_endstoreph where code='CX031150N-05' and colorid='302'
) b
where g_endstoreph.shopid=b.shopid and g_endstoreph.code=b.code and g_endstoreph.xsize=b.xsize
and g_endstoreph.code='CX031150N-05' and g_endstoreph.colorid='96';</p>
<p>--删除302色号
update g_endstoreph set c_id='del'
from (
select shopid,code,xsize,amount from g_endstoreph where code='CX031150N-05' and colorid='96'
) b
where g_endstoreph.shopid=b.shopid and g_endstoreph.code=b.code and g_endstoreph.xsize=b.xsize
and g_endstoreph.code='CX031150N-05' and g_endstoreph.colorid='302';
delete from g_endstoreph where code='CX031150N-05' and colorid='302' and c_id='del';</p>
<p>--更新302色号为96
update g_endstoreph set colorid='96' where code='CX031150N-05' and colorid='302';</p>
<p>--期货表
--合并已存在96色号的商品数量
update g_endstoreqh set amount=coalesce(g_endstoreqh.amount,0)+coalesce(b.amount,0)
from (
select shopid,code,xsize,amount from g_endstoreqh where code='CX031150N-05' and colorid='302'
) b
where g_endstoreqh.shopid=b.shopid and g_endstoreqh.code=b.code and g_endstoreqh.xsize=b.xsize
and g_endstoreqh.code='CX031150N-05' and g_endstoreqh.colorid='96';</p>
<p>--删除302色号
update g_endstoreqh set c_upztm='del'
from (
select shopid,code,xsize,amount from g_endstoreqh where code='CX031150N-05' and colorid='96'
) b
where g_endstoreqh.shopid=b.shopid and g_endstoreqh.code=b.code and g_endstoreqh.xsize=b.xsize
and g_endstoreqh.code='CX031150N-05' and g_endstoreqh.colorid='302';
delete from g_endstoreqh where code='CX031150N-05' and colorid='302' and c_upztm='del';</p>
<p>--更新302色号为96
update g_endstoreqh set colorid='96' where code='CX031150N-05' and colorid='302';</p>
<p>--采购汇总表
--合并已存在96色号的商品数量
update g_endstorecgex set amount=coalesce(g_endstorecgex.amount,0)+coalesce(b.amount,0)
from (
select shopid,code,xsize,amount from g_endstorecgex where code='CX031150N-05' and colorid='302'
) b
where g_endstorecgex.shopid=b.shopid and g_endstorecgex.code=b.code and g_endstorecgex.xsize=b.xsize
and g_endstorecgex.code='CX031150N-05' and g_endstorecgex.colorid='96';</p>
<p>--删除302色号
update g_endstorecgex set c_upztm='del'
from (
select shopid,code,xsize,amount from g_endstorecgex where code='CX031150N-05' and colorid='96'
) b
where g_endstorecgex.shopid=b.shopid and g_endstorecgex.code=b.code and g_endstorecgex.xsize=b.xsize
and g_endstorecgex.code='CX031150N-05' and g_endstorecgex.colorid='302';
delete from g_endstorecgex where code='CX031150N-05' and colorid='302' and c_upztm='del';</p>
<p>--更新302色号为96
update g_endstorecgex set colorid='96' where code='CX031150N-05' and colorid='302';</p>
<p>--进销存单据表
--合并已存在96色号的商品数量
update g_iodetail set amount=coalesce(g_iodetail.amount,0)+coalesce(b.amount,0),sale=(coalesce(g_iodetail.amount,0)+coalesce(b.amount,0))*g_iodetail.price
from (
select scripno,code,xsize,amount from g_iodetail where code='CX031150N-05' and colorid='302'
) b
where g_iodetail.scripno=b.scripno and g_iodetail.code=b.code and g_iodetail.xsize=b.xsize
and g_iodetail.code='CX031150N-05' and g_iodetail.colorid='96';</p>
<p>--删除302色号
update g_iodetail set c_beiz='del'
from (
select scripno,code,xsize,amount from g_iodetail where code='CX031150N-05' and colorid='96'
) b
where g_iodetail.scripno=b.scripno and g_iodetail.code=b.code and g_iodetail.xsize=b.xsize
and g_iodetail.code='CX031150N-05' and g_iodetail.colorid='302';
delete from g_iodetail where code='CX031150N-05' and colorid='302' and c_beiz='del';</p>
<p>--更新302色号为96
update g_iodetail set colorid='96' where code='CX031150N-05' and colorid='302';</p>
<p>--非进销存单据表
--合并已存在96色号的商品数量
update g_iodetailbill set amount=coalesce(g_iodetailbill.amount,0)+coalesce(b.amount,0),sale=(coalesce(g_iodetailbill.amount,0)+coalesce(b.amount,0))*g_iodetailbill.price
from (
select scripno,code,xsize,amount from g_iodetailbill where code='CX031150N-05' and colorid='302'
) b
where g_iodetailbill.scripno=b.scripno and g_iodetailbill.code=b.code and g_iodetailbill.xsize=b.xsize
and g_iodetailbill.code='CX031150N-05' and g_iodetailbill.colorid='96';</p>
<p>--删除302色号
update g_iodetailbill set c_beiz='del'
from (
select scripno,code,xsize,amount from g_iodetailbill where code='CX031150N-05' and colorid='96'
) b
where g_iodetailbill.scripno=b.scripno and g_iodetailbill.code=b.code and g_iodetailbill.xsize=b.xsize
and g_iodetailbill.code='CX031150N-05' and g_iodetailbill.colorid='302';
delete from g_iodetailbill where code='CX031150N-05' and colorid='302' and c_beiz='del';</p>
<p>--更新302色号为96
update g_iodetailbill set colorid='96' where code='CX031150N-05' and colorid='302';</p>
<p>--零售小票表
--合并已存在96色号的商品数量
update g_iodetail_zmd set amount=coalesce(g_iodetail_zmd.amount,0)+coalesce(b.amount,0),sale=(coalesce(g_iodetail_zmd.amount,0)+coalesce(b.amount,0))*g_iodetail_zmd.price
from (
select scripno,code,xsize,amount from g_iodetail_zmd where code='CX031150N-05' and colorid='302'
) b
where g_iodetail_zmd.scripno=b.scripno and g_iodetail_zmd.code=b.code and g_iodetail_zmd.xsize=b.xsize
and g_iodetail_zmd.code='CX031150N-05' and g_iodetail_zmd.colorid='96';</p>
<p>--删除302色号
update g_iodetail_zmd set c_beiz='del'
from (
select scripno,code,xsize,amount from g_iodetail_zmd where code='CX031150N-05' and colorid='96'
) b
where g_iodetail_zmd.scripno=b.scripno and g_iodetail_zmd.code=b.code and g_iodetail_zmd.xsize=b.xsize
and g_iodetail_zmd.code='CX031150N-05' and g_iodetail_zmd.colorid='302';
delete from g_iodetail_zmd where code='CX031150N-05' and colorid='302' and c_beiz='del';</p>
<p>--更新302色号为96
update g_iodetail_zmd set colorid='96' where code='CX031150N-05' and colorid='302';</p>
<p>--更新商品表
update g_spdm set c_huoj='02,11,32,53,67,81,91,93,96',d_upid=now() where code='CX031150N-05';</p>