MySql两表关联更新update示例SQL语句(用一个表更新另一个表)！

admin • • SQL技巧

MySql两表关联更新update示例SQL语句(用一个表更新另一个表)！

 作者：我来整一篇
这篇文章主要介绍了MySql两表关联更新update示例SQL语句的相关资料,文中分享了两种处理方式（保留/清空未匹配数据）,演示触发器记录更新操作至audit表,并通过示例SQL展示不同场景下更新效果及注意事项,需要的朋友可以参考下。

前言

本文介绍了如何通过SQL语句实现两个表之间的关联更新，具体涉及city表和people表。city表包含城市代码和名称，people表包含人员信息及其所在城市的代码和名称。需求是根据city表更新people表中的城市名称。文章提供了两种更新方式：一种是在未匹配到关联数据时保留原有数据，另一种是未匹配时清空原有数据。此外，还介绍了如何通过触发器记录更新操作，并创建了审计表people_audit来存储更新前后的数据。文章通过示例SQL语句展示了不同情况下的更新效果，并总结了更新时的注意事项。

两表关联更新update (用一个表更新另一个表)

表及数据

  • 建表及数据SQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    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表

code name
1 北京
2 上海
3 深圳
4 南京
5 广州
6 成都
7 重庆

people表

pp_id pp_name city_code city_name
1 john 1 北京
2 timo 2
3 张三 3 合肥
4 李四 8
5 王二麻 9 黑龙江

需求

根据city表的code和name，更新people的city_name。

创建触发器

为了方便查看更新了那些行数据，为people表创建触发器

先创建记录people更新记录的审计表

1
2
3
4
5
6
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;

创建每一行更新后触发器

1
2
3
4
5
6
7
CREATE TRIGGER before_update_people
BEFORE UPDATE ON people
FOR EACH ROW
BEGIN
INSERT INTO people_audit(id, old_value, new_value, updated_at)
VALUES(OLD.pp_id, OLD.city_name, NEW.city_name, NOW());
END;

关联无匹配，保持原数据

1
2
3
UPDATE people p , city c
SET p.city_name = c.name
WHERE p.city_code = c.code

正常情况：city表的code唯一

执行上面sql，输出：

id old_value new_value updated_at
1 北京 北京 2024-5-13 10:19
2 上海 2024-5-13 10:19
3 合肥 深圳 2024-5-13 10:19

数据修改了三行，结论：

  • 代码对应的城市更新，对应错误的更正
  • city表中没有的城市，在people表里保持原数据，不会被清空

异常情况：city表的code不唯一

插入一个重复code的数据

1
insert into city values('003','合肥');

恢复people表到初始数据，再次执行上面的更新sql，可以发现与上面返回值一致。

推论：只取先匹配的一个值替换

关联无匹配，清空原数据

1
2
3
4
5
update people
set city_name = (
select min(name) -- 重复时匹配其中一个
from city
where code = people.city_code)

或者

1
2
3
UPDATE people p
LEFT JOIN city c ON p.city_code=c.`code`
SET p.city_name = c.`name`

正常情况：city表的code唯一

id old_value new_value updated_at
1 北京 北京 2024-5-13 10:26
2 上海 2024-5-13 10:26
3 合肥 深圳 2024-5-13 10:26
4 2024-5-13 10:26
5 黑龙江 2024-5-13 10:26

数据修改了5行，结论：

  • 代码对应的城市更新，对应错误的更正
  • city表中没有的城市，在people表里全被更新为null

异常情况：city表的code不唯一

不会报错，会选匹配其中一个更新。

结论

更新时未匹配到关联数据

未匹配，保留原有数据

1
2
3
UPDATE people p , city c  -- 两张表
SET p.city_name = c.name   -- 更新值
WHERE p.city_code = c.code -- 条件

未匹配，清空原有数据

1
2
3
4
5
update people
set city_name = (
select min(name) -- 重复时匹配其中一个
from city
where code = people.city_code) 

或者

1
2
3
UPDATE people p -- 要更新的表
LEFT JOIN city c ON p.city_code=c.`code` -- 关联取数据的表
SET p.city_name = c.`name` --更新表字段

总结

到此这篇关于MySql两表关联更新update示例SQL语句的文章就介绍到这了。

 

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

Excelbook.cn Excel技巧 SQL技巧 Python 学习！

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需129元，即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>