update 代码仅仅更新一行数据
下面是我的update代码，不知道为何仅仅只更新了一行数据。UPDATE jqrwd SET jqrwd.mat =jqrwd.mat+jq_1.mat,jqrwd.lab =jqrwd.lab+jq_1.lab,jqrwd.burden =jqrwd.burden+jq_1.burden,jqrwd.sub =jqrwd.sub+jq_1.sub,jqrwd.bqll =jqrwd.bqll+jq_1.amt,jqrwd.mei ='step6: jq_1 根据第一次更新之后的jiaquan表汇总可以核算的任务单并核算出来。' from jiaquan WHERE jqrwd.no =jq_1.rwd
下面是前后代码：
SELECT t1.pn,t3.des,SUM(t1.amt)amt,SUM(t1.qty)qty,SUM(t1.amt)/SUM(t1.qty) price,SUM(t1.mat)/SUM(t1.qty)mat,SUM(t1.lab)/SUM(t1.qty)lab,SUM(t1.burden)/SUM(t1.qty) burden,SUM(t1.sub)/SUM(t1.qty) sub;
FROM jiaquan t1 LEFT JOIN jqrwd t2 ON t1.pn =t2.pn LEFT JOIN item t3 ON t1.pn =t3.pn WHERE t1.type in ('期初','外购入库单据','其他入库单据','产品入库单据','委外加工入库单据') AND t1.hs ='是'AND !ISBLANK(t2.mei);
AND t1.pn NOT in (SELECT distinc pn FROM jiaquan WHERE type in ('产品入库单据','委外加工入库单据') AND ISBLANK(mei) AND hs ='是');
GROUP BY 1,2 ORDER BY 5 INTO TABLE jiaquan_2
*update jiaquan_(生产领料单据、委外加工发出单据)_jiaquan_2
UPDATE jiaquan SET mat = jiaquan_2.mat*jiaquan.qty,lab =jiaquan_2.lab* jiaquan.qty,burden =jiaquan_2.burden*jiaquan.qty,sub=jiaquan_2.sub* jiaquan.qty,amt =jiaquan_2.price*jiaquan.qty,mei ='step4: 第一次更新生产领料、委外发出单价。' ;
from jiaquan_2 WHERE jiaquan.pn =jiaquan_2.pn AND jiaquan.type in ('生产领料单据','委外加工发出单据') AND (jiaquan_2.qty>0 AND jiaquan_2.mat>=0 AND jiaquan_2.lab>=0 AND jiaquan_2.burden >=0 AND jiaquan_2.sub >=0 AND jiaquan_2.price>=0) AND ISBLANK(jiaquan.mei) AND jiaquan.hs ='是'
*上一步骤中需要更新的物料没有更新，现在用标准成本补充。
SELECT distinc pn FROM jiaquan WHERE type in ('生产领料单据','委外加工发出单据') AND jiaquan.pn NOT in (SELECT distinc pn FROM jiaquan WHERE type in ('产品入库单据','委外加工入库单据') AND ISBLANK(mei) AND hs ='是') AND hs ='是' AND ISBLANK(mei) INTO CURSOR ycpn
*更新上一步骤未实现的物料
UPDATE jiaquan SET mat = stdcost.mat*jiaquan.qty,lab =stdcost.lab* jiaquan.qty,burden =stdcost.burden*jiaquan.qty,sub=stdcost.sub* jiaquan.qty,amt =stdcost.price*jiaquan.qty,mei='step5: 更新step4未实现物料' ;
from stdcost WHERE jiaquan.pn =stdcost.pn AND jiaquan.type in ('生产领料单据','委外加工发出单据') AND ISBLANK(mei) AND jiaquan.hs ='是' AND jiaquan.pn in (select pn from ycpn )
*当月有生产 成本未全部ok
SELECT distinc t1.no FROM jqrwd t1 LEFT JOIN jiaquan t2 ON t1.no =t2.po WHERE t2.hs ='是' AND ISBLANK(t2.mei) AND t2.type in ('生产领料单据','委外加工发出单据') INTO TABLE ycpn1
*汇总jiaquan表中可以核算的任务单成本。
SELECT distinc po rwd,SUM(t1.mat) mat,SUM(t1.lab)lab,SUM(t1.burden)burden,SUM(t1.sub)sub,SUM(t1.amt) amt FROM jiaquan t1 LEFT JOIN jqrwd t2 ON t1.po =t2.no WHERE t1.po not in (SELECT no FROM ycpn1) AND !isnull(t2.no) GROUP BY 1 ORDER BY 3 INTO TABLE jq_1
*更新jqrwd
UPDATE jqrwd SET jqrwd.mat =jqrwd.mat+jq_1.mat,jqrwd.lab =jqrwd.lab+jq_1.lab,jqrwd.burden =jqrwd.burden+jq_1.burden,jqrwd.sub =jqrwd.sub+jq_1.sub,jqrwd.bqll =jqrwd.bqll+jq_1.amt,jqrwd.mei ='step6: jq_1 根据第一次更新之后的jiaquan表汇总可以核算的任务单并核算出来。' from jiaquan WHERE jqrwd.no =jq_1.rwd
