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

前言

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

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

表及数据

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_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;

关联无匹配，保持原数据

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

正常情况：city表的code唯一

执行上面sql，输出：

数据修改了三行，结论：

异常情况：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表的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` --更新表字段

总结 

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