SQL批量插入、修改

最新推荐文章于 2024-11-16 23:10:01 发布

转载最新推荐文章于 2024-11-16 23:10:01 发布 · 232 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/panyw/p/10980935.html

1. 批量插入

insert into table_A ( ORDER_CODE, PAYMENT_CODE) values
<foreach item="item" index="index" collection="list" separator=",">
(#{item.ORDER_CODE},if(#{item.PAYMENT_CODE}='',NULL,#{item.PAYMENT_CODE}))

2.批量更新

update table_A set
PDH_CNT=
<foreach collection="list" item="item" index="index" separator=" " open="case EFH_SN" close="end">
when #{item.efhSn,jdbcType=VARCHAR} then #{item.pdhCnt,jdbcType=INTEGER}
</foreach>
,DUE_DT=
<foreach collection="list" item="item" index="index" separator=" " open="case EFH_SN" close="end">
when #{item.efhSn,jdbcType=VARCHAR} then #{item.dueDt,jdbcType=TIMESTAMP}
</foreach>
where del_flg=0 and EFH_SN in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
#{item.efhSn,jdbcType=VARCHAR}
</foreach>

3. table_A 数据导入table_B

INSERT INTO table_B (COMPANY_CODE,ORDER_CODE,PAYMENT_CODE,USER_CODE)

　　 SELECT COMPANY_CODE,ORDER_CODE,PAYMENT_CODE,USER_CODE FROM table_A

4.清空表table_A

truncate table table_A

转载于:https://www.cnblogs.com/panyw/p/10980935.html