要批量更新,指定值的
- 工作小总结
- 时间:2024-06-06 16:57
- 888人已阅读
🔔🔔🔔好消息!好消息!🔔🔔🔔
有需要的朋友👉:联系凯哥
需求:
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
。删除临时表:清理临时表以释放资源。
这种方法通过将子查询结果放入临时表,并对其进行索引优化,可以显著提高更新操作的效率。