mysql将两张表的两个列连接后更新到第三张表中

需求，现在有小区表community

单元楼表flat

房间表

现在要将community中小区地址和flat中门牌号拼接更新到room表中fullAddress中。

实现如下：

UPDATE room r
LEFT JOIN flat f ON f.id=r.flatID
LEFT JOIN community c ON c.id=f.communityID
SET r.fullAddress= CONCAT(c.address,f.num)

运行结果：

如果有条件，直接在后面添加where语句即可。

测试数据：

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50623
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50623
File Encoding         : 65001

Date: 2016-06-11 08:10:40
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `community`
-- ----------------------------
DROP TABLE IF EXISTS `community`;
CREATE TABLE `community` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `address` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of community
-- ----------------------------
INSERT INTO `community` VALUES ('1', '西雅图小区', '徐汇区三江路88弄');
INSERT INTO `community` VALUES ('2', '王家堂小区', '斜土路2561弄1-17号');
INSERT INTO `community` VALUES ('3', '东亚大楼', '斜土路2570号');
INSERT INTO `community` VALUES ('4', '四天小区', '中山南路918弄48-49号');
INSERT INTO `community` VALUES ('5', '田林二村', '田林路65弄510幢13-17单号');

-- ----------------------------
-- Table structure for `flat`
-- ----------------------------
DROP TABLE IF EXISTS `flat`;
CREATE TABLE `flat` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `communityID` varchar(30) DEFAULT NULL,
  `num` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of flat
-- ----------------------------
INSERT INTO `flat` VALUES ('1', '1', '3号1201室');
INSERT INTO `flat` VALUES ('2', '1', '3号1202室');
INSERT INTO `flat` VALUES ('3', '1', '3号1205室');
INSERT INTO `flat` VALUES ('4', '2', '5号501室');
INSERT INTO `flat` VALUES ('5', '2', '5号506室');
INSERT INTO `flat` VALUES ('6', '2', '5号504室');
INSERT INTO `flat` VALUES ('7', '3', '12号205室');
INSERT INTO `flat` VALUES ('8', '3', '12号102室');
INSERT INTO `flat` VALUES ('9', '3', '12号101室');

-- ----------------------------
-- Table structure for `results`
-- ----------------------------
DROP TABLE IF EXISTS `results`;
CREATE TABLE `results` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `year` int(11) DEFAULT NULL,
  `subject` int(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of results
-- ----------------------------
INSERT INTO `results` VALUES ('1', '2001', '1', '43');
INSERT INTO `results` VALUES ('2', '2001', '2', '45');
INSERT INTO `results` VALUES ('3', '2001', '3', '54');
INSERT INTO `results` VALUES ('4', '2001', '4', '63');
INSERT INTO `results` VALUES ('5', '2002', '1', '34');
INSERT INTO `results` VALUES ('6', '2002', '2', '32');
INSERT INTO `results` VALUES ('7', '2002', '3', '23');
INSERT INTO `results` VALUES ('8', '2002', '4', '54');

-- ----------------------------
-- Table structure for `room`
-- ----------------------------
DROP TABLE IF EXISTS `room`;
CREATE TABLE `room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `flatID` int(11) DEFAULT NULL,
  `fullAddress` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of room
-- ----------------------------
INSERT INTO `room` VALUES ('1', '1', null);
INSERT INTO `room` VALUES ('2', '2', null);
INSERT INTO `room` VALUES ('3', '3', null);
INSERT INTO `room` VALUES ('4', '4', null);
INSERT INTO `room` VALUES ('5', '5', null);
INSERT INTO `room` VALUES ('6', '6', null);
INSERT INTO `room` VALUES ('7', '7', null);
INSERT INTO `room` VALUES ('8', '8', null);
INSERT INTO `room` VALUES ('9', '9', null);
INSERT INTO `room` VALUES ('10', '10', null);