批量修改货品资料的类别

winxml

用ERP一个一个改,太慢. 整了个批量更改货品类别的代码.大家可以参考下.本来要好多天才能完成的9000多种商品资料修改类别的工作，结果，不到半天就全改完了。极大的提高了效率
基本原理是,找到货品类别的ID,然后先把所有商品都归到一个小类中,再分离出,名字相似的为另一个小类..
如我的,先把所有的油漆归为辅料类,然后开始一句一句地分离出汽车漆、津彩、津卫等漆为品牌漆这个小类别。最后再手工大致看下看有没分错类别的。

1. 
   
2. 油漆:
   
3. use sd201n_2008;update goods set goodstypeid='7'  where [goodstypeid] ='1'
   
4. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%汽车漆%'
   
5. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%调和漆%'
   
6. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%津彩%'
   
7. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%津卫%'
   
8. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%必劲%'
   
9. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%五一%'
   
10. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%必丽%'
    
11. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%圣君%'
    
12. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%ATC%'
    
13. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%来威%'
    
14. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%三棵树%'
    
15. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%华润%'
    
16. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%美乐士%'
    
17. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%中亲%'
    
18. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%展辰%'
    
19. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%大钢685%'
    
20. use sd201n_2008;update goods set goodstypeid='6'  where [goodstypeid] ='7' and [name] like '%双月双组分漆%'
    
21. 
    
22. use sd201n_2008;update goods set goodstypeid='7'  where [name] like '%爱国者砂%'
    
23. 
    
24. 
    
25. 
    
26. 五金:
    
27. use sd201n_2008;update goods set goodstypeid='10'  where [goodstypeid] ='2'
    
28. 
    
29. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%扳手%'
    
30. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%钳%'
    
31. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%锤%'
    
32. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%锉%'
    
33. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%尺%'
    
34. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%鸡尾锯%'
    
35. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%锯弓%'
    
36. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%锯套扳%'
    
37. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%螺丝刀%'
    
38. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%螺刀%'
    
39. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%套筒%'
    
40. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%玻璃刀%'
    
41. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%钻头%'
    
42. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%千斤%'
    
43. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%钢钎%'
    
44. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%掀%'
    
45. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%鸟刨%'
    
46. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%铲%'
    
47. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%钻头%'
    
48. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%木刨%'
    
49. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%泥抹%'
    
50. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%线坠%'
    
51. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%拉铆枪%'
    
52. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%划针%'
    
53. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%墨斗%'
    
54. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%烙铁%'
    
55. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%气筒%'
    
56. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%扩孔器%'
    
57. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%梯%'
    
58. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%焊把%'
    
59. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%斧%'
    
60. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%瓦刀%'
    
61. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%剪%'
    
62. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%仲兴%'
    
63. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%面罩%'
    
64. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%连套%'
    
65. use sd201n_2008;update goods set goodstypeid='8'  where [goodstypeid] ='10' and [name] like '%镊%'
    
66. 
    
67. 
    
68. 
    
69. 家俱类:
    
70. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%拉手%'
    
71. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%移门%'
    
72. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%夹板%'
    
73. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%拉杆%'
    
74. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%扣手%'
    
75. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%包角%'
    
76. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%包边%'
    
77. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%床花%'
    
78. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%纹纸%'
    
79. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%凌形%'
    
80. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%铁扣%'
    
81. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%明扣%'
    
82. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%暗扣%'
    
83. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%猫眼%'
    
84. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%木梢%'
    
85. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%木棒%'
    
86. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%门顶%'
    
87. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%铅笔%'
    
88. use sd201n_2008;update goods set goodstypeid='9'  where [goodstypeid] ='10' and [name] like '%门吸%'
    
89. 
    
90. 设定:停用的商品归为停用类:
    
91. use sd201n_2008;update goods set goodstypeid='4'  where [closed] ='T'

复制代码

gjbox

好像在同一个数据库上操作哦。一个
use sd201n_2008 ：
go
.....
go
不行吗？ 我也是新人 交流下

winxml

嘿嘿。 是啊。 偷懒，下句复制上句的。。。然后稍微改下就继续了。。

lookice

重要提醒：任何对后台的修改，首要备份数据！其次保留数据库操作语句！不然哭都来不急！

本帖子由阳光论坛管理组成员答复[增加管理津贴4个]

打造出国内一流的行业软件应用交流论坛－－阳光网驿助力企业信息化

wjf156324

如果这样改了，会出现数据库质疑吗？？

qazwsxedc

提醒:说明你的ERP是什么软件的什么版本?你这样发出来,没有一点意义!

本帖子由阳光论坛管理组成员答复[增加管理津贴4个]

打造出国内一流的行业软件应用交流论坛－－阳光网驿助力企业信息化

realciw

帮楼主补充,是以速达3000系列软件为例的