sql - Mysql:是否有一个更新语句将反规范化如下？

Question

对于以下(简化的)mysql 数据库设置，我想将适用的 guid 复制到 message 表中。这可以通过单个 SQL update 完成吗？

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `guid` varchar(13) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `user` (`id`, `guid`) VALUES
(1, 'a'),
(2, 'b'),
(3, 'c');


CREATE TABLE IF NOT EXISTS `message` (
  `user` int(11) NOT NULL,
  `user_guid` varchar(13) NOT NULL,
  KEY `user` (`user`)
) ENGINE=InnoDB;
ALTER TABLE `message`
  ADD CONSTRAINT `message_ibfk_1` FOREIGN KEY (`user`) REFERENCES `user` (`id`) ON DELETE CASCADE;

INSERT INTO `message` (`user`, `user_guid`) VALUES
(3, ''),
(2, ''),
(3, '');

Answer 1

更新 m SET m.user_guid = u.guid FROM message m INNER JOIN user u ON u.id = m.user

以上是针对 MS SQL 的。

对于 mySQL 试试这个:

UPDATE FROM message as m INNER JOIN user as u ON u.id = m.user
SET m.user_guid = u.guid

或者后者后者:

UPDATE message as m SET m.user_guid = u.guid
FROM message INNER JOIN user as u ON u.id = m.user