ITPub博客

ibatis批量更新点滴

原创 MySQL 作者:drgfml 时间:2018-07-12 17:12:26 0 删除 编辑

第一种批量更新范例:按单字段条件配合case语句更新

update hbsm.t_sm_saleapply_benef

set dictate_id=

>iterate conjunction="" open="case bnfcry_line_id" close="end">

>![CDATA[

when #list[].bnfcryLineId# then #list[].dictateId#

]]>

>/iterate>

where bnfcry_line_id in

>iterate conjunction="," open="(" close=")">

>![CDATA[

#list[].bnfcryLineId#

]]>

>/iterate>

这种批量更新产生的sql语句是这样的

UPDATE hbsm.t_sm_saleapply_benef

SET dictate_id = CASE bnfcry_line_id

WHEN '001' THEN

'dic001'

WHEN '002' THEN

'dic002'

END

WHERE bnfcry_line_id IN ('001', '002');


第二种批量更新范例:按多字段条件配合case语句更新

update hbsm.t_sm_trustcontract_benef

set benef_id=

>iterate conjunction="" open="case" close="end">

>![CDATA[

when prod_contract_no=#list[].prodContractNo# 

                                    and bnfcry_profit_id=#list[].bnfcryProfitId# 

then #list[].benefId#

]]>

>/iterate>

where 1=1 and 

>iterate conjunction="or">

>![CDATA[

(

 prod_contract_no=#list[].prodContractNo# 

and bnfcry_profit_id=#list[].bnfcryProfitId#

)

]]>

>/iterate>

这种批量更新产生的sql语句是这样的

UPDATE hbsm.t_sm_trustcontract_benef

SET benef_id = CASE

WHEN prod_contract_no = '010'

AND bnfcry_profit_id = '01' THEN

'P0000041batch'

WHEN prod_contract_no = '020'

AND bnfcry_profit_id = '02' THEN

'P0015403batch'

END

WHERE

1 = 1

AND (prod_contract_no = '010'

AND bnfcry_profit_id = '01'

)

OR (prod_contract_no = '020'

AND bnfcry_profit_id = '02'

)

                

请登录后发表评论 登录
全部评论
  • 博文量
    27
  • 访问量
    32520