【mybatis】mysql级联更新两个表或多张表的数据

例如 info表和relation表有关联，现在要在一个sql语句中同时级联更新两张表的数据

update
    security_code_info info
LEFT JOIN
    security_code_relation re
ON
    re.security_code = info.security_code
set 
    info.code_stock_state = 9,
    info.code_state = 9,
    info.code_qa_state = 9,
    info.code_act_date = '2018-01-01',
    info.receipt_date = '2018-01-01',
    re.receipt_uid = '111',
    re.depot_id = '111'
where 
    info.security_code = '100032014010987164';

 

批量操作更新级联操作的sql：

 <update id="receipt" parameterType="com.pisen.cloud.luna.ms.security.code.base.mapper.beans.ReceiptBean">
        update
            security_code_info info
        LEFT JOIN
            security_code_relation re
        ON
            re.security_code = info.security_code
        set
            info.code_stock_state = ${@com.pisen.cloud.luna.ms.security.code.base.domain.SecurityCodeInfo@IN_STOCK},
            info.code_state = ${@com.pisen.cloud.luna.ms.security.code.base.domain.SecurityCodeInfo@ACTIVATION_EN},
            info.code_qa_state = ${@com.pisen.cloud.luna.ms.security.code.base.domain.SecurityCodeInfo@DEFAULT},
            info.code_act_date = #{receiptAndActDate},
            info.receipt_date = #{receiptAndActDate},
            re.receipt_uid = #{receiptUid},
            re.depot_id = #{depotUid}
        where
            info.security_code
        in
        <foreach collection="scList" index="index" close=")" open="(" item="item" separator=",">
            #{item}
        </foreach>

    </update>

 

转载于:https://www.cnblogs.com/sxdcgaq8080/p/9639362.html