mysql批量更新

例如我们平时使用mysql的批量更新都是这样的

    
        UPDATE product_third_code_mapping
        SET stock_num = #{p.stockNum},
        update_time = NOW()
        WHERe source_channel = #{p.channelCode}
        AND third_product_code = #{p.skuId}
    

但是这样的sql会影响效率
所以我们需要结合case when then else end来进行优化

 UPDATE product
        SET third_merchant_product_code = ( CASE
        
            WHEN id = #{p.storeProductId} then #{p.thirdProductCode}
        
        ELSE 0 END )
        WHERe
        is_deleted = 0
        AND id IN (
        
            #{p}
        
        )

如果需要更新多个字段

   update im_virtual_channel_stock iv
        set iv.virtual_stock_num = (
        case
        
            when iv.item_id = #{p.storeProductId} then #{p.stockNum}
        
        else 0 end
        ),
        iv.virtual_available_stock_num = (
        case
        
            when iv.item_id = #{p.storeProductId} then #{p.stockNum} - iv.freeze_stock_num
        
        else 0 end
        )
        where iv.is_available=1
        and iv.is_deleted=0
        and iv.item_id in (
        
            #{p.storeProductId}
        
        )