bys更新语句

原创

qvodnet 2019-02-21 16:28:53 博主文章分类：时空 ©著作权

©著作权归作者所有：来自51CTO博客作者qvodnet的原创作品，请联系作者获取转载授权，否则将追究法律责任

--插入所需数据 insert INTO xtsw_ym.dbo.splsk select plh, dj_sn, spid, rq, dwbh, yishj, yjzh, djbh, bm, ywy, '', zhy, jzhh, hw, pihao, rkshl, rkdj, rkje, chkshl, chkje, xshe, xshje, ml, jcshl, jcje, chbdj, lshj, hwjcsh, hwjcer, plcj, '', bendian, '', '0.00' from openrowset( 'SQLOLEDB', '192.168.16.237'; 'SA'; 'zdyysm,.',ymksoa.dbo.splsk) where (djbh like 'jha%' or djbh like 'jhc%' or djbh like 'xsa%' or djbh like 'xsc%')
and spid in (select spid From spkfk where shengccj like '%广州白云山和记黄埔中药有限公司%') and rq>='2018-12-26' and rq<='2019-02-19' order by plh

--检查数据条目 select * From splsk where djbh like 'xs%' and spid in (select spid From spkfk where shengccj like '%广州白云山和记黄埔中药有限公司%')

--删除相应数据 delete a from splsk a,fr_splskbys190219 b,spkfk c where b.备注1='删除' and a.djbh=b.单据编号 and a.spid=c.spid and b.商品编号=c.spbh and a.chkshl=b.数量 and a.pihao=b.批号

---变更数据 select xshe/xshje,xshe/chkshl,chkshl,xshe,xshje,* from splsk where djbh in ('XSAYMA00326092') and spid in (select spid From spkfk where shengccj like '%广州白云山和记黄埔中药有限公司%')

select * From mchk where danwbh in ('XSZD02869','XSZD00019','XSZD01517','XSZD00376')

update splsk set dwbh='DWI00000160' where djbh in ('XSAYMA00321322','XSAYMA00323252','XSAYMA00322730','XSAYMA00326695','XSAYMA00326695','XSAYMA00326772','XSAYMA00326772') update splsk set dwbh='DWI00000862' where djbh in ('XSAYMA00323072') and spid='SPH00000099' update splsk set dwbh='DWI00013329' where djbh in ('XSAYMA00319245') and spid='SPH00000099' update splsk set dwbh='DWI00005650' where djbh in ('XSAYMA00322823') and spid='SPH00000099' update splsk set dwbh='DWI00005650' where djbh in ('XSAYMA00323297') and spid='SPH00000099'

update splsk set chkshl=-500,xshe=75chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00319889') and spid='SPH00019860' update splsk set chkshl=662,xshe=14.04chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00319155') and spid='SPH00016850' update splsk set chkshl=82,xshe=13chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00320299') and spid='SPH00010963' update splsk set chkshl=1,xshe=8.67chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00320602') and spid='SPH00007030' update splsk set chkshl=120,xshe=11.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00320827') and spid='SPH00000185' update splsk set chkshl=3307,xshe=11.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00320993') and spid='SPH00000185' update splsk set chkshl=600,xshe=27.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00321322') and spid='SPH00000099' update splsk set chkshl=-1250,xshe=76chkshl,xshje=xshe/1.16,pihao='H18A010' where djbh in ('XSAYMA00321710') and spid='SPH00019860' and dj_sn=2 update splsk set chkshl=2800,xshe=27.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00323072') and spid='SPH00000099' update splsk set chkshl=944,xshe=27.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00323252') and spid='SPH00000099' update splsk set chkshl=120,xshe=11.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00324716') and spid='SPH00000185' update splsk set chkshl=60,xshe=12.08chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00327868') and spid='SPH00000185' update splsk set chkshl=-20,xshe=27.5chkshl,xshje=xshe/1.16 where djbh in ('XSCYMA00034799') and spid='SPH00000099' update splsk set chkshl=-1150,xshe=76chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00326092') and spid='SPH00019860' and dj_sn=4 update splsk set chkshl=-150,xshe=76*chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00326092') and spid='SPH00019860' and dj_sn=3

select * From splsk where spid in (select spid From spkfk where shengccj like '%广州白云山和记黄埔中药有限公司%') and djbh like 'xs%' and chkshl<0 and zhy='销售出库单' order by rq desc

update splsk set zhy='销售退回入库单',djbh=replace(djbh,'XSA','XSC') where spid in (select spid From spkfk where shengccj like '%广州白云山和记黄埔中药有限公司%') and djbh like 'xs%' and chkshl<0 and zhy='销售出库单'

update fr_splskbys190325 set chkshl=100,xshe=11.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00331860') and spid='SPH00000185' update fr_splskbys190325 set chkshl=200,xshe=12.08chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00331088') and spid='SPH00000185' update fr_splskbys190325 set chkshl=200,xshe=12.08chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00337041') and spid='SPH00000185' and dj_sn=3 update fr_splskbys190325 set chkshl=100,xshe=12.08chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00330290') and spid='SPH00000185' update fr_splskbys190325 set chkshl=4,xshe=12.08chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00333577') and spid='SPH00000185' update fr_splskbys190325 set chkshl=140,xshe=12.08chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00334920') and spid='SPH00000185' update fr_splskbys190325 set chkshl=60,xshe=12.08chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00330920') and spid='SPH00000185' update fr_splskbys190325 set chkshl=82,xshe=12.08chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00334741') and spid='SPH00000185' update fr_splskbys190325 set chkshl=60,xshe=12.08chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00334761') and spid='SPH00000185' update fr_splskbys190325 set chkshl=500,xshe=11.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00336417') and spid='SPH00000185' and dj_sn=8 update fr_splskbys190325 set chkshl=20,xshe=11.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00335239') and spid='SPH00000185' update fr_splskbys190325 set chkshl=23,xshe=11.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00336510') and spid='SPH00000185' update fr_splskbys190325 set chkshl=100,xshe=11.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00336564') and spid='SPH00000185' update fr_splskbys190325 set chkshl=1200,xshe=13.65chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00329620') and spid='SPH00011710' update fr_splskbys190325 set chkshl=1200,xshe=13.65chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00329634') and spid='SPH00011710' update fr_splskbys190325 set chkshl=2000,xshe=27.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00333978') and spid='SPH00000099' update fr_splskbys190325 set chkshl=600,xshe=27.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00335300') and spid='SPH00000099' update fr_splskbys190325 set chkshl=600,xshe=27.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00336478') and spid='SPH00000099' update fr_splskbys190325 set chkshl=4000,xshe=27.5chkshl,xshje=xshe/1.16,pihao='K18A028' where djbh in ('XSAYMA00336893') and spid='SPH00000099' update fr_splskbys190325 set chkshl=25,xshe=27.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00337115') and spid='SPH00000099' update fr_splskbys190325 set chkshl=660,xshe=27.5chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00330968') and spid='SPH00000099' update fr_splskbys190325 set chkshl=46,xshe=9.4chkshl,xshje=xshe/1.16 where djbh in ('XSAYMA00331974') and spid='SPH00000180'

update fr_splskbys190325 set dwbh='DWI00000211' where djbh='XSAYMA00336417' and spid='SPH00000888' update fr_splskbys190325 set dwbh='DWI00000211' where djbh='XSAYMA00333913' update fr_splskbys190325 set dwbh='DWI00000211' where djbh='XSAYMA00333847' update fr_splskbys190325 set dwbh='DWI00000208' where djbh='XSAYMA00333978' update fr_splskbys190325 set dwbh='DWI00000201' where djbh='XSAYMA00335300' update fr_splskbys190325 set dwbh='DWI00000862' where djbh='XSAYMA00336478' update fr_splskbys190325 set dwbh='DWI00000160' where djbh='XSAYMA00336893' update fr_splskbys190325 set dwbh='DWI00013329' where djbh='XSAYMA00330968'

-----直接用数据导入先将变更数量为空的都改为0 select * into #1 from splsk where 1=2

select * from fr_splsk20190610bys where 备注1='变更' and 数量变更<>0 select * from fr_splsk20190610bys where 备注1='变更' and 批号变更<>'' select * from fr_splsk20190610bys where 备注1='变更' and 单位编码<>''

update fr_splsk20190610bys set 单位编号=单位编码 where 备注1='变更' and 单位编码<>'' update fr_splsk20190610bys set 批号=批号变更 where 备注1='变更' and 批号变更<>'' update fr_splsk20190610bys set 数量=数量变更,含税金额=数量变更*含税价 where 备注1='变更' and 数量变更<>0

insert into #1 select '',100,b.spid,a.日期,d.dwbh,'否','否',a.单据编号,a.部门名称,a.业务员,'','销售出库单','JZH00000050','HWI00000025',a.批号,0,0,0,a.数量,0,a.含税金额,a.含税金额,a.毛利,0,0,0,0,0,0,0,'','YMA','',0 from fr_splsk20190610bys a,spkfk b,mchk d where a.商品编号=b.spbh and a.单位编号=d.danwbh and (a.备注1='变更' or a.备注1 is null)

update #1 set djbh=replace(djbh,'XSA','XSC') where xshe<0 and djbh like 'XSA%' update #1 set zhy='销售退回入库单' where djbh like 'XSC%'

insert into splsk select * from #1 order by djbh,rq