MySql中批量更新-批量更换字段

批量更新

话不多说，直接上代码；
（需求：根据之前excel导入的数据，根据一列的状态值修改另一列的日期。）
mybatis

  <update id="updateTimeLine" parameterType="java.util.List">
        update t_ybx_bill_timeline
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="pass_send_biz_date=case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <choose>
                        <when test="i.passSendBizDate != null">
                            when is_pass_send_biz = 1 and bill_id = #{i.billId} then #{i.passSendBizDate}
                        </when>
                        <otherwise>
                            when is_pass_send_biz = 0 and bill_id = #{i.billId} then null
                        </otherwise>
                    </choose>
                </foreach>
            </trim>
            <trim prefix="pass_busine_audit_date=case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <choose>
                        <when test="i.passBusineAuditDate != null">
                     when is_busine_audit = 1 and bill_id = #{i.billId}  then #	{i.passBusineAuditDate}
                        </when>
                        <otherwise>
                            when is_busine_audit = 0 and bill_id = #{i.billId}  then null
                        </otherwise>
                    </choose>
                </foreach>
            </trim>
            <trim prefix="pass_share_audit_date=case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <choose>
                        <when test="i.passShareAuditDate != null">
                            when is_share_audit = 1 and bill_id = #{i.billId}  then #{i.passShareAuditDate}
                        </when>
                        <otherwise>
                            when is_share_audit = 0 and bill_id = #{i.billId}  then null
                        </otherwise>
                    </choose>
                </foreach>
            </trim>
            <trim prefix="wait_pay_date=case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <choose>
                        <when test="i.waitPayDate != null">
                            when is_wait_pay = 1 and bill_id = #{i.billId}  then #{i.passShareAuditDate}
                        </when>
                        <otherwise>
                            when is_wait_pay = 0 and bill_id = #{i.billId}  then null
                        </otherwise>
                    </choose>
                </foreach>
            </trim>
            <trim prefix="final_pay_date=case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <choose>
                        <when test="i.finalPayDate != null ">
                            when is_final_pay = 1 and bill_id = #{i.billId} then #{i.finalPayDate}
                        </when>
                        <otherwise>
                            when is_final_pay = 0 and bill_id = #{i.billId}  then null
                        </otherwise>
                    </choose>
                </foreach>
            </trim>
        </trim>
        where bill_id in
        <foreach collection="list" item="i" index="index" open="(" separator="," close=")">
             #{i.billId}
        </foreach>
    </update>

可以参考这篇文章 添加链接描述

批量更换字段

由于导入时数据字段混乱导致mysql数据库表中字段不匹配，
表中状态值交换，利用创建时间用来分隔。

set @time = '2020-12-14 12:00:00';
update t_ybx_contract as t1 ,t_ybx_contract as t2
set t1.state_alias=t2.state_value,
     t1.state_value=t2.state_alias
 where t1.ours_contract_id = t2.ours_contract_id
   and t1.ours_create_time < @time;

有的表中没有创建时间则用ID进行筛选
交换 lastId:最后一条导入的数据id。

 set @lastId = 8303;
 update table1 as t1,table1 t2
 set t1.colum1 =t2.colum2,
     t1.colum3 =t2.colum4,
     t1.colum2=t2.colum1 ,
     t1.colum4=t2.colum3 
 where t1.id= t2.id
   and t1.id< @lastId;