MySql-两表关联更新update (用一个表更新另一个表)_mysql update关联表更新
本文介绍了如何通过SQL语句实现两个表之间的关联更新，具体涉及city表和people表。city表包含城市代码和名称，people表包含人员信息及其所在城市的代码和名称。需求是根据city表更新people表中的城市名称。文章提供了两种更新方式：一种是在未匹配到关联数据时保留原有数据，另一种是未匹配时清空原有数据。此外，还介绍了如何通过触发器记录更新操作，并创建了审计表people_audit来存储更新前后的数据。文章通过示例SQL语句展示了不同情况下的更新效果，并总结了更新时的注意事项。
两表关联更新update (用一个表更新另一个表)
表及数据
-
建表及数据SQL
SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for city-- ----------------------------DROP TABLE IF EXISTS `city`;CREATE TABLE `city` ( `code` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of city-- ----------------------------INSERT INTO `city` VALUES (\'001\', \'北京\');INSERT INTO `city` VALUES (\'002\', \'上海\');INSERT INTO `city` VALUES (\'003\', \'深圳\');INSERT INTO `city` VALUES (\'004\', \'南京\');INSERT INTO `city` VALUES (\'005\', \'广州\');INSERT INTO `city` VALUES (\'006\', \'成都\');INSERT INTO `city` VALUES (\'007\', \'重庆\');SET FOREIGN_KEY_CHECKS = 1; SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for people-- ----------------------------DROP TABLE IF EXISTS `people`;CREATE TABLE `people` ( `pp_id` int NULL DEFAULT NULL, `pp_name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `city_code` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `city_name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of people-- ----------------------------INSERT INTO `people` VALUES (1, \'john\', \'001\', \'北京\');INSERT INTO `people` VALUES (2, \'timo\', \'002\', \'\');INSERT INTO `people` VALUES (3, \'张三\', \'003\', \'合肥\');INSERT INTO `people` VALUES (4, \'李四\', \'008\', \'\');INSERT INTO `people` VALUES (5, \'王二麻\', \'009\', \'黑龙江\');SET FOREIGN_KEY_CHECKS = 1;
city表
people表
需求
根据city表的code和name，更新people的city_name。
创建触发器
为了方便查看更新了那些行数据，为people表创建触发器
先创建记录people更新记录的审计表
CREATE TABLE `people_audit` ( `id` int DEFAULT NULL, `old_value` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `new_value` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `updated_at` datetime DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;创建每一行更新后触发器
CREATE TRIGGER before_update_peopleBEFORE UPDATE ON peopleFOR EACH ROWBEGIN INSERT INTO people_audit(id, old_value, new_value, updated_at) VALUES(OLD.pp_id, OLD.city_name, NEW.city_name, NOW());END;关联无匹配，保持原数据
UPDATE people p , city cSET p.city_name = c.name WHERE p.city_code = c.code 正常情况：city表的code唯一
执行上面sql，输出：
数据修改了三行，结论：
- 代码对应的城市更新，对应错误的更正
- city表中没有的城市，在people表里保持原数据，不会被清空
异常情况：city表的code不唯一
插入一个重复code的数据
insert into city values(\'003\',\'合肥\');恢复people表到初始数据，再次执行上面的更新sql，可以发现与上面返回值一致。
推论：只取先匹配的一个值替换
关联无匹配，清空原数据
update people set city_name = ( select min(name) -- 重复时匹配其中一个 from city where code = people.city_code)或者
UPDATE people p LEFT JOIN city c ON p.city_code=c.`code`SET p.city_name = c.`name`正常情况：city表的code唯一
数据修改了5行，结论：
- 代码对应的城市更新，对应错误的更正
- city表中没有的城市，在people表里全被更新为null
异常情况：city表的code不唯一
不会报错，会选匹配其中一个更新。
结论
更新时未匹配到关联数据
未匹配，保留原有数据
UPDATE people p , city c -- 两张表SET p.city_name = c.name -- 更新值WHERE p.city_code = c.code -- 条件未匹配，清空原有数据
update people set city_name = ( select min(name) -- 重复时匹配其中一个 from city where code = people.city_code) 或者
UPDATE people p -- 要更新的表LEFT JOIN city c ON p.city_code=c.`code` -- 关联取数据的表SET p.city_name = c.`name` --更新表字段
