多表关联的批量更新与删除方法

大概总结一下关于多表关联的批量更新与删除方法和注意事项

 

执行方法格式如下：

一、join...on...写法

update——UPDATE TABLE_1 AS A LEFT JOIN TABLE_2 B ON A.TID = B.TID SET A.PARAM_VALUE = 0 WHERE 1 = 1;

 

delete——DELETE TABLE_1 AS A, TABLE_2 AS B FROM TABLE_1 AS A LEFT JOIN TABLE_2 AS B ON A.TID = B.TID WHERE 1 = 1;

 

二、where a.id = b.id 链接写法

update——UPDATE TABLE_1 A, TABLE_2 AS B SET A.PARAM_VALUE = 1, B.PARAM_VALUE = 1 WHERE A.TID = B.TID AND 1 = 1;

 

delete——DELETE TABLE_1 AS A, TABLE_2 AS B FROM TABLE_1 AS A, TABLE_2 AS B WHERE A.TID = B.TID AND 1 = 1;

 

 

注意事项：

1、尽量不要用IN来包含ID标识执行查询，那样会使索引失效

2、执行语句之前先用SELECT查询获取COUNT看执行前后是否一致