MYSQL中批量update

目录复制

数据准备：test4表和test5表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test4
-- ----------------------------
DROP TABLE IF EXISTS `test4`;
CREATE TABLE `test4`  (
  `high` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test4
-- ----------------------------
INSERT INTO `test4` VALUES ('A');
INSERT INTO `test4` VALUES ('B');
INSERT INTO `test4` VALUES ('C');
INSERT INTO `test4` VALUES ('D');
INSERT INTO `test4` VALUES ('E');

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test5
-- ----------------------------
DROP TABLE IF EXISTS `test5`;
CREATE TABLE `test5`  (
  `old` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `newold` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`old`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test5
-- ----------------------------
INSERT INTO `test5` VALUES ('A', 'NEWA11');
INSERT INTO `test5` VALUES ('B', 'NEWB21');
INSERT INTO `test5` VALUES ('C', 'NEWC31');
INSERT INTO `test5` VALUES ('D', 'NEWD41');

SET FOREIGN_KEY_CHECKS = 1;

方法一

UPDATE test4 
SET high = ( SELECT newold FROM test5 WHERE old = test4.high ) 

方法二

UPDATE test4  RIGHT JOIN test5  ON ( test4.high=test5.old)
SET test4.high=test5.newold

方法三

UPDATE test4 t4  ,( SELECT * FROM test5 ) t5 
SET t4.high = t5.newold 
WHERE
	t4.high = t5.old

结果