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

来源：互联网发布：mysql 增加unique 编辑：程序博客网时间：2024/06/10 03:15

需求，现在有小区表community

单元楼表flat

房间表

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

实现如下：

UPDATE room rLEFT JOIN flat f ON f.id=r.flatIDLEFT JOIN community c ON c.id=f.communityIDSET r.fullAddress= CONCAT(c.address,f.num)

运行结果：

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

测试数据：

/*Navicat MySQL Data TransferSource Server         : localhostSource Server Version : 50623Source Host           : localhost:3306Source Database       : testTarget Server Type    : MYSQLTarget Server Version : 50623File Encoding         : 65001Date: 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);

0 0