mysql表a字段更新为表b字段id关联

数据准备

DROP TABLE IF EXISTS `testa`;
CREATE TABLE `testa` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of testa
-- ----------------------------
INSERT INTO `testa` VALUES ('1', 'a1', '1');
INSERT INTO `testa` VALUES ('2', 'a2', '2');
INSERT INTO `testa` VALUES ('3', 'a3', '3');
INSERT INTO `testa` VALUES ('4', 'a4', '4');

DROP TABLE IF EXISTS `testb`;
CREATE TABLE `testb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of testb
-- ----------------------------
INSERT INTO `testb` VALUES ('1', 'b1', '11');
INSERT INTO `testb` VALUES ('2', 'b2', '22');
INSERT INTO `testb` VALUES ('3', 'b3', '33');

将testa表的name,age字段更变更新为testb表的字段name,age; id关联

执行该sql

update testa, testb set testa.`name`=testb.`name`, testa.age=testb.age where testa.id= testb.id;

结果