金蝶K3 新旧物料编码转换 SQL语句

--根据新物料编码规则，通过新老编码对照表更新 --合法性检查 --对照表新编码有重复 select * --update wl_n_o set fnote = fnote + '*新编码重复' from wl_n_o where fnumber_new in (select fnumber_new from wl_n_o group by fnumber_new having count(fnumber_new)>1) --删除老编码为空的记录 delete wl_n_o where fnumber_old is null --看有多少条记录 select * from wl_n_o --看可以比对到的有多少条记录，如有不一致，说明EXCEL中的老编码有错 select t1.fnumber,t2.* from t_icitem t1 inner join wl_n_o t2 on t1.fnumber = t2.fnumber_old --检查哪几条没有比对到 select t2.fnumber,t1.* from wl_n_o t1 left join t_icitem t2 on t1.fnumber_old = t2.fnumber where t2.fnumber is null --检查k3中有，但对照表中缺少的物料 select t1.fnumber fnumber_old,t1.fname fname_old,t1.fmodel fmodel_old into wl_lack from t_icitem t1 left join wl_n_o t2 on t1.fnumber = t2.fnumber_old where t2.fnumber_old is null --检查物料新代码的上级组是否存在，如没有上级组，请手工增加 select reverse(right(reverse(fnumber_new),(len(fnumber_new)-charindex('.',reverse(fnumber_new),0)))), * from wl_n_o where reverse(right(reverse(fnumber_new),(len(fnumber_new)-charindex('.',reverse(fnumber_new),0)))) not in (select fnumber from t_item where fdetail = 0 and fitemclassid = 4) --t_icitem 已经发生异常了 select fnumber + '_' + cast(fitemid as varchar),* --update t_icitem set fnumber = fnumber + '_' + cast(fitemid as varchar) from t_icitem where fnumber in (select fnumber from t_icitem group by fnumber having count(fnumber)>1) select fnumber + '_' + cast(fitemid as varchar),* --update t_icitem set fnumber = fnumber + '_' + cast(fitemid as varchar) from t_item where fnumber in select * from t_item where fnumber in (select fnumber from t_item where fitemclassid = 4 group by fnumber having count(fnumber)>1) order by select * FROM t_item where fitemclassid = 4 and fdetail = 1 --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! --更新 将t_icitem上的fnumber 更新至t_item表 select t1.fnumber,t2.fnumber,* --update t_item set fnumber = t2.fnumber from t_item t1 inner join t_icitem t2 on t1.fitemid = t2.fitemid where t1.fitemclassid = 4 and t1.fnumber <> t2.fnumber and t2.fnumber like '04%' order by t2.fnumber select * update wl_n_o set fnote = '新编码编写不正确应该为01.01.04.01.开头' from wl_n_o where fnumber_new like '01.01.03.%' --还有以下物料未成功转换 select t2.fnumber fnumber_new,t1.fnumber fnumber_old,t2.fname,fmodel --into wl_nochange from t_item t1 inner join t_icitem t2 on t1.fitemid = t2.fitemid where not t1.fnumber like '0%' and fitemclassid = 4 and fdetail = 1 order by t2.fnumber select * from wl_n_o2 where fnumber_old in (select fnumber_old from wl_nochange) order by fnumber_old select * from t_item where fitemclassid = 4 and fdetail = 1 and fnumber like '0%' select * from t_icitem_bak --开始更新 --如果都有了上级组 --1.1、更新t_icitem 的fnumber select t2.*,t1.* --update t_icitemcore set fnumber = t2.fnumber_new from t_icitemcore t1 inner join wl_n_o t2 on t1.fnumber = t2.fnumber_old where t2.fnote is null --1.2、更新t_icitem短代码 fshortNumber select top 100 reverse(left(reverse(fnumber),charindex('.',reverse(fnumber),0)-1)),* --update t_icitem set fshortnumber = reverse(left(reverse(fnumber),charindex('.',reverse(fnumber),0)-1)) from t_icitem where fshortnumber <> reverse(left(reverse(fnumber),charindex('.',reverse(fnumber),0)-1)) --1.3、更新t_icitem上级组 fparentID select reverse(right(reverse(t1.fnumber),(len(t1.fnumber)-charindex('.',reverse(t1.fnumber),0)))), t1.fnumber,t1.fname,t1.fmodel,t1.fparentid,t2.fnumber,t2.fname,t2.fitemid,t1.* --update t_icitemcore set fparentid = t2.fitemid from t_icitemcore t1 inner join (select * from t_item where fdetail = 0 and fitemclassid = 4) t2 on reverse(right(reverse(t1.fnumber),(len(t1.fnumber)-charindex('.',reverse(t1.fnumber),0)))) = t2.fnumber where t1.fparentid <> t2.fitemid -- 更新t_item表的fparentid select t2.fparentid,t1.fparentid,* --update t_item set fparentid = t1.fparentid from t_icitem t1 inner join t_item t2 on t1.fitemid = t2.fitemid where t1.fparentid <> t2.fparentid --3、更新t_item上级组 fparentID select reverse(right(reverse(t1.fnumber),(len(t1.fnumber)-charindex('.',reverse(t1.fnumber),0)))), t1.fnumber,t1.fname,t1.fparentid,t2.fnumber,t2.fname,t2.fitemid,t1.* --update t_item set fparentid = t2.fitemid from t_item t1 inner join (select * from t_item where fdetail = 0 and fitemclassid = 4) t2 on reverse(right(reverse(t1.fnumber),(len(t1.fnumber)-charindex('.',reverse(t1.fnumber),0)))) = t2.fnumber where t1.fparentid <> t2.fitemid and t1.fitemclassid = 4 and t1.fdetail = 1 --4、更新 成本对象代码 fnumber、fshortNumber select t1.fnumber,t1.fname,t2.fnumber,t2.fname,t2.fmodel -- update cbcostobj set fnumber = t2.fnumber,fshortnumber = t2.fshortNumber from cbcostobj t1 inner join t_icitem t2 on t1.fstdproductid = t2.fitemid where t1.fnumber <> t2.fnumber --5、更新 成本对象上级组 fparentID select reverse(right(reverse(t1.fnumber),(len(t1.fnumber)-charindex('.',reverse(t1.fnumber),0)))), t1.fnumber,t1.fname,t1.fparentid,t2.fnumber,t2.fname,t2.fitemid,t1.* --update t_icitem set fparentid = t2.fitemid from cbcostobj t1 inner join (select * from t_item where fitemclassid = 2001 and fdetail = 0 ) t2 on reverse(right(reverse(t1.fnumber),(len(t1.fnumber)-charindex('.',reverse(t1.fnumber),0)))) = t2.fnumber where t1.fparentid <> t2.fitemid