需求,现在有小区表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);