mysql连表更新

[superman_happy]

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `expert_copy`
-- ----------------------------
DROP TABLE IF EXISTS `expert_copy`;
CREATE TABLE `expert_copy` (
`id` int(11) NOT NULL,
`field1` varchar(500) DEFAULT NULL COMMENT '技术领域1',
`field2` varchar(500) DEFAULT NULL COMMENT '技术领域1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of expert_copy
-- ----------------------------
INSERT INTO `expert_copy` VALUES ('1', '桥梁工程','');
INSERT INTO `expert_copy` VALUES ('1', '桥梁工程','');
INSERT INTO `expert_copy` VALUES ('1', '桥梁工程','');
INSERT INTO `expert_copy` VALUES ('2', '气象','');
INSERT INTO `expert_copy` VALUES ('3', '公路','');
INSERT INTO `expert_copy` VALUES ('3', '公路','');
INSERT INTO `expert_copy` VALUES ('4', '制药','');


SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `temp`
-- ----------------------------
DROP TABLE IF EXISTS `temp`;
CREATE TABLE `temp` (
`id` int(11) NOT NULL,
`field1` varchar(500) DEFAULT NULL COMMENT '技术领域1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of temp
-- ----------------------------
INSERT INTO `temp` VALUES ('1', 'A');
INSERT INTO `temp` VALUES ('1', 'B');
INSERT INTO `temp` VALUES ('2', 'D');
INSERT INTO `temp` VALUES ('3', 'E');
INSERT INTO `temp` VALUES ('3', 'F');
INSERT INTO `temp` VALUES ('4', 'G');


mysql根据temp表 更新expert_copy表的field2字段，要求一一对应，
执行后expert_copy 表应为：

id field1 field2
1 桥梁工程 A
1 桥梁工程 B
1 桥梁工程 null或空
2 气象 D
3 公路 E
3 公路 F
4 制药 G

[ACMAIN_CHM]

楼主的测试数据自己测试过吗！！！

[ACMAIN_CHM]

http://topic.csdn.net/u/20101230/15/c544ee84-bbce-4ce3-84d9-1ac55dab79ad.html

这不是第一次楼主提供错误的数据了。 尽可能自己测试一下，否则是浪费别人的时间。

[superman_happy]

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `expert_copy`
-- ----------------------------
DROP TABLE IF EXISTS `expert_copy`;
CREATE TABLE `expert_copy` (
`id` int(11) NOT NULL,
`field1` varchar(500) DEFAULT NULL COMMENT '技术领域1',
`field2` varchar(500) DEFAULT NULL COMMENT '技术领域1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of expert_copy
-- ----------------------------
INSERT INTO `expert_copy` VALUES ('1', '桥梁工程','');
INSERT INTO `expert_copy` VALUES ('1', '桥梁工程','');
INSERT INTO `expert_copy` VALUES ('1', '桥梁工程','');
INSERT INTO `expert_copy` VALUES ('2', '气象','');
INSERT INTO `expert_copy` VALUES ('3', '公路','');
INSERT INTO `expert_copy` VALUES ('3', '公路','');
INSERT INTO `expert_copy` VALUES ('4', '制药','');


SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `temp`
-- ----------------------------
DROP TABLE IF EXISTS `temp`;
CREATE TABLE `temp` (
`id` int(11) NOT NULL,
`field1` varchar(500) DEFAULT NULL COMMENT '技术领域1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of temp
-- ----------------------------
INSERT INTO `temp` VALUES ('1', 'A');
INSERT INTO `temp` VALUES ('1', 'B');
INSERT INTO `temp` VALUES ('2', 'D');
INSERT INTO `temp` VALUES ('3', 'E');
INSERT INTO `temp` VALUES ('3', 'F');
INSERT INTO `temp` VALUES ('4', 'G');

[ACMAIN_CHM]

需要在你的表中添加主键。否则无法区别
| 1 | 桥梁工程 | |
| 1 | 桥梁工程 | |
| 1 | 桥梁工程 | |

[WWWWA]

在`expert_copy`、`temp`中加入唯一标识的字段，比如自增ID

[superman_happy]

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `expert_copy`
-- ----------------------------
DROP TABLE IF EXISTS `expert_copy`;
CREATE TABLE `expert_copy` (
`record_id` int(11) NOT NULL AUTO_INCREMENT,
`id` int(11) NOT NULL,
`field1` varchar(500) DEFAULT NULL COMMENT '技术领域1',
`field2` varchar(500) DEFAULT NULL COMMENT '技术领域1',
PRIMARY KEY (`record_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of expert_copy
-- ----------------------------
INSERT INTO `expert_copy` VALUES ('1', '1', '桥梁工程', '');
INSERT INTO `expert_copy` VALUES ('2', '1', '桥梁工程', '');
INSERT INTO `expert_copy` VALUES ('3', '1', '桥梁工程', '');
INSERT INTO `expert_copy` VALUES ('4', '2', '气象', '');
INSERT INTO `expert_copy` VALUES ('5', '3', '公路', '');
INSERT INTO `expert_copy` VALUES ('6', '3', '公路', '');
INSERT INTO `expert_copy` VALUES ('7', '4', '制药', '');

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `temp`
-- ----------------------------
DROP TABLE IF EXISTS `temp`;
CREATE TABLE `temp` (
`record_id` int(11) NOT NULL AUTO_INCREMENT,
`id` int(11) NOT NULL,
`field1` varchar(500) DEFAULT NULL COMMENT '技术领域1',
PRIMARY KEY (`record_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of temp
-- ----------------------------
INSERT INTO `temp` VALUES ('1', '1', 'A');
INSERT INTO `temp` VALUES ('2', '1', 'B');
INSERT INTO `temp` VALUES ('3', '2', 'D');
INSERT INTO `temp` VALUES ('4', '3', 'E');
INSERT INTO `temp` VALUES ('5', '3', 'F');
INSERT INTO `temp` VALUES ('6', '4', 'G');

[ACMAIN_CHM]

楼主确认这次不会再改测试数据了？！

建议还是再改一次吧。否则差之毫厘，谬之千里


问题说明越详细，回答也会越准确！参见如何提问。（提问的智慧）

[superman_happy]

只是用同一批数据，来提问几个问题

[ACMAIN_CHM]

不会再次改变你的测试数据了？ 否则回答完后，楼主只需要再随便贴点什么，别人等于白忙乎。

[WWWWA]

UPDATE expert_copy b3
INNER JOIN
(
SELECT a.record_id,a.id,a.field1,a.field2,COUNT(a1.id) AS pm FROM expert_copy a LEFT JOIN
expert_copy a1 ON a.id=a1.id AND a.record_id>=a1.record_id GROUP BY a.record_id,a.id,a.field1,a.field2) b1
ON b3.record_id=b1.record_id
INNER JOIN
(SELECT a.field1,a.id,a.record_id,COUNT(a1.record_id) AS pm FROM `temp` a LEFT JOIN `temp` a1 ON a.id=a1.id AND a.record_id>=a1.record_id
GROUP BY a.field1,a.id,a.record_id) b2
ON b1.id=b2.id AND b1.pm=b2.pm
SET b3.field2=b2.field1

[小小小小周]

[Quote=引用 10 楼 wwwwa 的回复:]
UPDATE expert_copy b3
INNER JOIN
(
SELECT a.record_id,a.id,a.field1,a.field2,COUNT(a1.id) AS pm FROM expert_copy a LEFT JOIN
expert_copy a1 ON a.id=a1.id AND a.record_id>=a1.record_id GROUP BY a.……
[/Quote]


好强~