MySQL:根据A表更新B表的数据

郎家岭伯爵 2023年08月09日 546次浏览

背景

遇到这样一个场景:在数据库里有两个表,需要根据 source 表中的数据来刷新 target 表中的数据,两表可通过某个字段进行关联(例如通过 user_id 进行关联)。

实现

例如表结构如下:

source 表结构:

CREATE TABLE `user_20230224` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户自增ID',
  `deleted` tinyint(4) NOT NULL DEFAULT 0 COMMENT '是否删除',
  `user_name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `password` varchar(20) NOT NULL DEFAULT '' COMMENT '用户密码',
  `create_time` datetime NOT NULL DEFAULT current_timestamp() COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '修改时间',
  PRIMARY KEY (`user_id`) USING BTREE,
  KEY `主键索引` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COMMENT='用户表';

target 表结构:

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户自增ID',
  `deleted` tinyint(4) NOT NULL DEFAULT 0 COMMENT '是否删除',
  `user_name` varchar(1000) NOT NULL DEFAULT '' COMMENT '用户名',
  `password` varchar(255) NOT NULL DEFAULT '' COMMENT '用户密码',
  `age` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT current_timestamp() COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '修改时间',
  PRIMARY KEY (`user_id`) USING BTREE,
  KEY `主键索引` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='用户表';

业务需求:

现在需要根据 source 表中 password 字段更新 target 表中的 password 字段,两表通过 user_id 进行关联。

实现该需求的 SQL 有如下写法:

写法一

UPDATE `user` AS t
JOIN user_20230224 AS s ON t.user_id = s.user_id 
SET t.`password` = s.`password`;

写法二

UPDATE `user` t,
user_20230224 s 
SET t.`password` = s.`password` 
WHERE t.user_id = s.user_id;

总结

在 MySQL 中跨表更新数据,根据 A 表更新 B 表数据的 SQL 写法。