A表的某个字段通过某些关联条件,覆盖到B表
update
global_dict a INNER JOIN feature_temp b on a.feature_val=b.compu_const
set a.conf_byte_val = b.lower_limit
场景
现在有两张表,分别是 global_dict (简称B),feature_temp (简称A)
A表结构
CREATE TABLE `feature_temp` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`lower_limit` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '下限',
`compu_const` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '常量',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1167 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
B表结构
CREATE TABLE `global_dict` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`feature_val` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`conf_byte_val` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL ,
`data_source` tinyint(1) NOT NULL DEFAULT '1'
PRIMARY KEY (`id`),
UNIQUE KEY `idx_unique` (`room_number`,`feature_val`) COMMENT '联合唯一索引',
KEY `idx_feature` (`feature_family_desc_en`),
KEY `idx_feature_family` (`feature_family`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8944 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
A表的feature_val字段与B表的compu_const字段为关联字段,需要通过这个条件把A表的lower_limit 覆盖到B表的conf_byte_val
可以通过这个sql实现:
update
global_dict a INNER JOIN feature_temp b on a.feature_val=b.compu_const
set a.conf_byte_val = b.lower_limit