前言

本文介紹了如何通過(guò)SQL語(yǔ)句實(shí)現(xiàn)兩個(gè)表之間的關(guān)聯(lián)更新，具體涉及city表和people表。city表包含城市代碼和名稱，people表包含人員信息及其所在城市的代碼和名稱。需求是根據(jù)city表更新people表中的城市名稱。文章提供了兩種更新方式：一種是在未匹配到關(guān)聯(lián)數(shù)據(jù)時(shí)保留原有數(shù)據(jù)，另一種是未匹配時(shí)清空原有數(shù)據(jù)。此外，還介紹了如何通過(guò)觸發(fā)器記錄更新操作，并創(chuàng)建了審計(jì)表people_audit來(lái)存儲(chǔ)更新前后的數(shù)據(jù)。文章通過(guò)示例SQL語(yǔ)句展示了不同情況下的更新效果，并總結(jié)了更新時(shí)的注意事項(xiàng)。

兩表關(guān)聯(lián)更新update (用一個(gè)表更新另一個(gè)表)

表及數(shù)據(jù)

• 建表及數(shù)據(jù)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表

需求

根據(jù)city表的code和name，更新people的city_name。

創(chuàng)建觸發(fā)器

為了方便查看更新了那些行數(shù)據(jù)，為people表創(chuàng)建觸發(fā)器

先創(chuàng)建記錄people更新記錄的審計(jì)表

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;

創(chuàng)建每一行更新后觸發(fā)器

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;

關(guān)聯(lián)無(wú)匹配，保持原數(shù)據(jù)

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

正常情況：city表的code唯一

執(zhí)行上面sql，輸出：

數(shù)據(jù)修改了三行，結(jié)論：

• 代碼對(duì)應(yīng)的城市更新，對(duì)應(yīng)錯(cuò)誤的更正
• city表中沒(méi)有的城市，在people表里保持原數(shù)據(jù)，不會(huì)被清空

異常情況：city表的code不唯一

插入一個(gè)重復(fù)code的數(shù)據(jù)

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

恢復(fù)people表到初始數(shù)據(jù)，再次執(zhí)行上面的更新sql，可以發(fā)現(xiàn)與上面返回值一致。

推論：只取先匹配的一個(gè)值替換

關(guān)聯(lián)無(wú)匹配，清空原數(shù)據(jù)

update people 
set city_name = (
                select min(name) -- 重復(fù)時(shí)匹配其中一個(gè)
                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唯一

數(shù)據(jù)修改了5行，結(jié)論：

• 代碼對(duì)應(yīng)的城市更新，對(duì)應(yīng)錯(cuò)誤的更正
• city表中沒(méi)有的城市，在people表里全被更新為null

異常情況：city表的code不唯一

不會(huì)報(bào)錯(cuò)，會(huì)選匹配其中一個(gè)更新。

結(jié)論

更新時(shí)未匹配到關(guān)聯(lián)數(shù)據(jù)

未匹配，保留原有數(shù)據(jù)

UPDATE people p , city c  -- 兩張表
SET p.city_name = c.name   -- 更新值
WHERE p.city_code = c.code -- 條件

未匹配，清空原有數(shù)據(jù)

update people 
set city_name = (
                select min(name) -- 重復(fù)時(shí)匹配其中一個(gè)
                from city
                where code = people.city_code)  

或者

UPDATE people p -- 要更新的表
LEFT JOIN city c ON p.city_code=c.`code` -- 關(guān)聯(lián)取數(shù)據(jù)的表
SET p.city_name = c.`name` --更新表字段

總結(jié) 

到此這篇關(guān)于MySql兩表關(guān)聯(lián)更新update示例SQL語(yǔ)句的文章就介紹到這了,更多相關(guān)MySql兩表關(guān)聯(lián)更新update內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論