第一种批量更新范例：按单字段条件配合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'
)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/96210/viewspace-2157765/ ，如需转载，请注明出处，否则将追究法律责任。
