有2张表:营业日报表:sc_tt_pos_info_report, 营业日报销售明细表:sc_tt_pos_dtl_report;
营业日报销售明细表sc_tt_pos_dtl_report 是 营业日报表:sc_tt_pos_info_report 的子表。营业日报表中有个字段:retail_total_amount, 记录的是该销售金额的总和,即子表中每天数据的消息金额之和。
营业日报子表也有个字段:retail_total_amount, 记录的是每条销售数据的销售金额。现在有部门数据,营业日报表中的retail_total_amount 和 子表中的每天数据的retail_total_amount相加后不相等,现在要修改成相等,将主表的retail_total_amount改成和子表的retail_total_amount相加之和。
update sc_tt_pos_info_report r
set r.retail_total_amount =
(select sum(d.retail_total_amount)
from sc_tt_pos_info_report pir, sc_tt_pos_dtl_report d
where pir.sc_tt_pos_info_report_id = d.sc_tt_pos_info_report_id
and r.sc_tt_pos_info_report_id = pir.sc_tt_pos_info_report_id
and pir.retail_total_amount <> sum(d.retail_total_amount)
);
营业日报销售明细表sc_tt_pos_dtl_report 是 营业日报表:sc_tt_pos_info_report 的子表。营业日报表中有个字段:retail_total_amount, 记录的是该销售金额的总和,即子表中每天数据的消息金额之和。
营业日报子表也有个字段:retail_total_amount, 记录的是每条销售数据的销售金额。现在有部门数据,营业日报表中的retail_total_amount 和 子表中的每天数据的retail_total_amount相加后不相等,现在要修改成相等,将主表的retail_total_amount改成和子表的retail_total_amount相加之和。
update sc_tt_pos_info_report r
set r.retail_total_amount =
(select sum(d.retail_total_amount)
from sc_tt_pos_info_report pir, sc_tt_pos_dtl_report d
where pir.sc_tt_pos_info_report_id = d.sc_tt_pos_info_report_id
and r.sc_tt_pos_info_report_id = pir.sc_tt_pos_info_report_id
and pir.retail_total_amount <> sum(d.retail_total_amount)
);
update sc_tt_pos_info_report r
set r.retail_total_amount =
(select sum(d.retail_total_amount)
from sc_tt_pos_info_report pir, sc_tt_pos_dtl_report d
where pir.sc_tt_pos_info_report_id = d.sc_tt_pos_info_report_id
and r.sc_tt_pos_info_report_id = pir.sc_tt_pos_info_report_id
and r.sale_no = '20110629165674PSR'
)
where exists (select 1 from sc_tt_pos_info_report pir, sc_tt_pos_dtl_report d
where pir.sc_tt_pos_info_report_id = d.sc_tt_pos_info_report_id
and r.sc_tt_pos_info_report_id = pir.sc_tt_pos_info_report_id
and r.sale_no = '20110629165674PSR'
);现在只能修改单一的,不能将整个不符合的修改过来啊
update sc_tt_pos_info_report r
set r.retail_total_amount =
(select sum(d.retail_total_amount)
from sc_tt_pos_info_report pir, sc_tt_pos_dtl_report d
where pir.sc_tt_pos_info_report_id = d.sc_tt_pos_info_report_id
and r.sc_tt_pos_info_report_id = pir.sc_tt_pos_info_report_id
and pir.retail_total_amount <> sum(d.retail_total_amount)
);这个语句不行么? 数据量多少? 如果少的话 就不用判断相等不相等了 直接更新·
SET r.retail_total_amount =
(SELECT SUM(d.retail_total_amount)
FROM sc_tt_pos_dtl_report d
WHERE r.sc_tt_pos_info_report_id = d.sc_tt_pos_info_report_id)
WHERE EXISTS
(SELECT 1
FROM sc_tt_pos_dtl_report d
WHERE r.sc_tt_pos_info_report_id = d.sc_tt_pos_info_report_id);
from sc_tt_pos_info_report r, (select sum(d.retail_total_amount) price, r1.sc_tt_pos_info_report_id id
from sc_tt_pos_info_report r1, sc_tt_pos_dtl_report d
where r1.sc_tt_pos_info_report_id = d.sc_tt_pos_info_report_id
group by r1.sc_tt_pos_info_report_id) a
where r.sc_tt_pos_info_report_id = a.id
and r.retail_total_amount <> a.price
MERGE INTO sc_tt_pos_info_report r
USING (SELECT sc_tt_pos_info_report_id,SUM(retail_total_amount) dtlsum
FROM sc_tt_pos_dtl_report
GROUP BY sc_tt_pos_info_report_id) d
ON (d.sc_tt_pos_info_report_id=r.sc_tt_pos_info_report_id)
WHEN MATCHED THEN
UPDATE SET r.retail_total_amount=d.dtlsum
WHERE r.retail_total_amount<>d.dtlsum;
update sc_tt_pos_info_report
set sc_tt_pos_info_report.retail_total_amount =
(select sum(sc_tt_pos_dtl_report.retail_total_amount)
from sc_tt_pos_dtl_report
where sc_tt_pos_dtl_report.sc_tt_pos_info_report_id =
sc_tt_pos_info_report.sc_tt_pos_info_report_id) where sc_tt_pos_info_report.retail_total_amount <>
(select sum(sc_tt_pos_dtl_report.retail_total_amount)
from sc_tt_pos_dtl_report
where sc_tt_pos_dtl_report.sc_tt_pos_info_report_id =
sc_tt_pos_info_report.sc_tt_pos_info_report_id)
临时表同时存上不相等的主表的主键 外加字表的金额和,再UPDATE 就快很多了