有一个这样的需求,营业日报中由于某门店客户入反了数据,现在要重新做一张单子,把销售改成退货,退货改成销售。
原来是退货的数据,ex:fact_amount为负数=-122,要改成122,反之销售的122改成-122.
还有改状态1改成2,2改成1.
查询出该门店的销售数据有744条。这744条都要修改。--该门店的销售数据
select d.*
from sc_tt_pos_dtl_report d,sc_tt_pos_info_report h --h,销售数据表;d,销售数据明细表(子表)
where d.sc_tt_pos_info_report_id=h.sc_tt_pos_info_report_id
and h.sc_tm_store_info_id=7247
已经新建了新的销售单,现在要将这744条数据插入到新的销售明细单中,
完成这个需求,insert和update语句。销售金额×-1,状态1改成2,2改成1外键pos_info_id改成新建的id,createdate,updatedate改成sysdateinsert into sc_tt_pos_dtl_report (*) dr values
(select d.*
from sc_tt_pos_dtl_report d,sc_tt_pos_info_report h
where d.sc_tt_pos_info_report_id=h.sc_tt_pos_info_report_id
and h.sc_tm_store_info_id=7247update sc_tt_pos_dtl_report dr set (dr.num, dr.receivable_amount, dr.fact_amount, dr.retail_total_amount,
dr.create_date, dr.update_date, dr.dtl_sale_type, dr.cost_amount) =
(select d.num*-1, d.receivable_amount*-1, d.fact_amount*-1, d.retail_total_amount*-1,
sysdate, sysdate, d.dtl_sale_type, d.cost_amount*-1
from sc_tt_pos_dtl_report d,sc_tt_pos_info_report h
where d.sc_tt_pos_info_report_id=h.sc_tt_pos_info_report_id
and h.sc_tm_store_info_id=7247
)
原来是退货的数据,ex:fact_amount为负数=-122,要改成122,反之销售的122改成-122.
还有改状态1改成2,2改成1.
查询出该门店的销售数据有744条。这744条都要修改。--该门店的销售数据
select d.*
from sc_tt_pos_dtl_report d,sc_tt_pos_info_report h --h,销售数据表;d,销售数据明细表(子表)
where d.sc_tt_pos_info_report_id=h.sc_tt_pos_info_report_id
and h.sc_tm_store_info_id=7247
已经新建了新的销售单,现在要将这744条数据插入到新的销售明细单中,
完成这个需求,insert和update语句。销售金额×-1,状态1改成2,2改成1外键pos_info_id改成新建的id,createdate,updatedate改成sysdateinsert into sc_tt_pos_dtl_report (*) dr values
(select d.*
from sc_tt_pos_dtl_report d,sc_tt_pos_info_report h
where d.sc_tt_pos_info_report_id=h.sc_tt_pos_info_report_id
and h.sc_tm_store_info_id=7247update sc_tt_pos_dtl_report dr set (dr.num, dr.receivable_amount, dr.fact_amount, dr.retail_total_amount,
dr.create_date, dr.update_date, dr.dtl_sale_type, dr.cost_amount) =
(select d.num*-1, d.receivable_amount*-1, d.fact_amount*-1, d.retail_total_amount*-1,
sysdate, sysdate, d.dtl_sale_type, d.cost_amount*-1
from sc_tt_pos_dtl_report d,sc_tt_pos_info_report h
where d.sc_tt_pos_info_report_id=h.sc_tt_pos_info_report_id
and h.sc_tm_store_info_id=7247
)
销售金额×-1 你已经做了
状态:Decode(d.状态,1,2,2,1)
至于:外键pos_info_id改成新建的id
只需要先删除旧的外键,再重新新建外键就行了
sc_tt_pos_info_report 销售数据表(主表)
老大说插入和更新可以放到一起。我不知道怎么写呀。。insert into sc_tt_pos_dtl_report
select d.*
from sc_tt_pos_dtl_report d,sc_tt_pos_info_report h
where d.sc_tt_pos_info_report_id=h.sc_tt_pos_info_report_id
and h.sc_tm_store_info_id=7247update sc_tt_pos_dtl_report dr set (dr.dtl_report_id, dr.info_report_id, dr.num, dr.receivable_amount, dr.fact_amount, dr.retail_total_amount,
dr.create_date, dr.update_date, dr.dtl_sale_type, dr.cost_amount) =
(select d.num*-1, d.receivable_amount*-1, d.fact_amount*-1, d.retail_total_amount*-1,
sysdate, sysdate, d.dtl_sale_type, d.cost_amount*-1
from sc_tt_pos_dtl_report d,sc_tt_pos_info_report h
where d.sc_tt_pos_info_report_id=h.sc_tt_pos_info_report_id
and h.sc_tm_store_info_id=7247
)
using(...)
when matched then update...
when not matched then insert ....
insert into sc_tt_pos_dtl_report
select d.*
from sc_tt_pos_dtl_report d,sc_tt_pos_info_report h
where d.sc_tt_pos_info_report_id=h.sc_tt_pos_info_report_id
and h.sc_tm_store_info_id=7247直接这样插入,是会报违反唯一键约束错误的,我的主键是序列自动生成的,外键已经有了,要改的还有上面这些字段。
潘哥上面写的看不懂了
唯一键约束是哪些啊,即你的sc_tt_pos_info_report和select的结果集的关联关系是怎么样的?
dr.product_no, dr.product_name, dr.k3_product_id, dr.color_id, dr.size_group, dr.unit,
dr.num, dr.price, dr.receivable_amount, dr.discount_rate, dr.fact_amount, dr.retail_price, dr.retail_total_amount,
dr.opt_counter, dr._for_delete, dr.create_date, dr.update_date, dr.dtl_sale_type,
dr.cost_price, dr.cost_amount
)
select seq_sc_tt_pos_dtl_report_id.nextval, 82917, SEQ_SC_TT_DTL_REPORT_NO_TMP.NEXTVAL, d.sc_tm_product_info_id, d.product_no, d.product_name,
d.k3_product_id, d.color_id, d.size_group, d.unit, d.num*-1, d.price, d.receivable_amount*-1, d.discount_rate, d.fact_amount*-1,
d.retail_price, d.retail_total_amount*-1, d.opt_counter, d._for_delete, sysdate, sysdate, Decode(d.dtl_sale_type,1,2,2,1),
d.cost_price, d.cost_amount*-1
from sc_tt_pos_dtl_report d, sc_tt_pos_info_report h
where d.sc_tt_pos_info_report_id=h.sc_tt_pos_info_report_id
and h.sc_tm_store_info_id=7247