要批量更新，指定值的
- 工作小总结
- 时间：2024-06-06 16:57
- 837人已阅读
🔔🔔🔔好消息!好消息!🔔🔔🔔
有需要的朋友👉:联系凯哥
需求：
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 = '七号楼'));
这里的逻辑如下：
首先创建一个子查询
sub_query，该查询会根据data_id和building_number获取新的param_id和building_number。在主查询中通过
JOIN连接子查询和t_devices_param_relation表。更新
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多秒
针对这个情况，可以进一步优化查询。尝试以下步骤：
使用临时表：将中间结果存储在临时表中以减少查询复杂度。
简化条件：进一步简化查询条件，确保最少的数据操作。
以下是使用临时表的示例：
-- 创建临时表
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;
这里是分步的详细解释：
创建临时表：将匹配的数据预先查询到临时表
temp_param_relation中。添加索引：在临时表上创建索引以加快 JOIN 操作。
执行更新操作：使用临时表中的数据更新主表
t_devices_param_relation。删除临时表：清理临时表以释放资源。
这种方法通过将子查询结果放入临时表，并对其进行索引优化，可以显著提高更新操作的效率。