要批量更新,指定值的

  • 作者: 凯哥Java(公众号:凯哥Java)
  • 工作小总结
  • 时间:2024-06-06 16:57
  • 888人已阅读
简介 需求:mysql数据库:updatet_devices_param_relationa setparam_id=(SELECTid fromt_devices_class_parm wheredata_id=7000 andbuilding_number=(SELECTidFROMt_business_dictWHERETYPE=4ANDNAME='

🔔🔔🔔好消息!好消息!🔔🔔🔔

有需要的朋友👉:联系凯哥 微信号 kaigejava2022


需求:

mysql数据库: update t_devices_param_relation a  set param_id = (SELECT id  from t_devices_class_parm  where data_id = 7000  and building_number = (SELECT id FROM t_business_dict WHERE TYPE = 4 AND NAME = '七号楼')) where a.building_number = 13 and a.param_id = 7000; 

 上面sql语句中的data_id = 7000和a.param_id = 7000 是一一对应的。

 现在我想要批量更新a.building_number = 13  怎么修改sql语句?


要批量更新 a.building_number = 13 的记录,你需要确保在子查询中能正确地匹配每个 data_id 和相应的 param_id。下面是一个改进的 SQL 语句,通过 JOIN 方式来批量更新:

UPDATE t_devices_param_relation a

JOIN (

    SELECT 

        b.id AS new_param_id, 

        b.data_id, 

        c.id AS new_building_number

    FROM t_devices_class_parm b

    JOIN t_business_dict c ON b.building_number = c.id

    WHERE c.type = 4 AND c.name = '七号楼'

) sub_query

ON a.param_id = sub_query.data_id

SET a.param_id = sub_query.new_param_id

WHERE a.building_number = 13 AND a.param_id IN (SELECT data_id FROM t_devices_class_parm WHERE building_number = (SELECT id FROM t_business_dict WHERE TYPE = 4 AND NAME = '七号楼'));

这里的逻辑如下:

  1. 首先创建一个子查询 sub_query,该查询会根据 data_idbuilding_number 获取新的 param_idbuilding_number

  2. 在主查询中通过 JOIN 连接子查询和 t_devices_param_relation 表。

  3. 更新 param_id 为新的 param_id,其中 a.building_number 为 13 且 a.param_id 在特定范围内。

这样做可以确保批量更新 a.building_number 为 13 的所有相关记录。



上面sql我优化后: 

 UPDATE t_devices_param_relation a JOIN (   

 SELECT        b.id AS new_param_id,        b.data_id,        b.building_number AS new_building_number    FROM t_devices_class_parm b     where b.building_number = 13 ) sub_query 

ON a.param_id = sub_query.data_id SET a.param_id = sub_query.new_param_id WHERE a.building_number = 13 AND a.param_id IN (SELECT data_id FROM t_devices_class_parm WHERE building_number =  13); 

 还是执行300多秒


针对这个情况,可以进一步优化查询。尝试以下步骤:

  1. 使用临时表:将中间结果存储在临时表中以减少查询复杂度。

  2. 简化条件:进一步简化查询条件,确保最少的数据操作。

以下是使用临时表的示例:


-- 创建临时表

CREATE TEMPORARY TABLE temp_param_relation AS

SELECT 

    b.id AS new_param_id, 

    b.data_id

FROM t_devices_class_parm b

WHERE b.building_number = 13;


-- 确保临时表创建成功后,添加索引以优化查询

CREATE INDEX idx_temp_param_relation_data_id ON temp_param_relation(data_id);


-- 执行更新操作

UPDATE t_devices_param_relation a

JOIN temp_param_relation sub_query

ON a.param_id = sub_query.data_id

SET a.param_id = sub_query.new_param_id

WHERE a.building_number = 13;


-- 删除临时表

DROP TEMPORARY TABLE temp_param_relation;


这里是分步的详细解释:

  1. 创建临时表:将匹配的数据预先查询到临时表 temp_param_relation 中。

  2. 添加索引:在临时表上创建索引以加快 JOIN 操作。

  3. 执行更新操作:使用临时表中的数据更新主表 t_devices_param_relation

  4. 删除临时表:清理临时表以释放资源。

这种方法通过将子查询结果放入临时表,并对其进行索引优化,可以显著提高更新操作的效率。


TopTop