MySQL多表更新实例

MySQL多表更新实例

更新`t_total`表中total列的结果：total减去与表`t_detail`中相应的model_name和year_month下的所有count的值。

1、建表 `t_total`

-- ----------------------------
-- Table structure for `t_total`
-- ----------------------------
DROP TABLE IF EXISTS `t_total`;
CREATE TABLE `t_total` (
  `model_name` varchar(20) NOT NULL,
  `year_month` varchar(10) NOT NULL,
  `total` int(8) NOT NULL,
  PRIMARY KEY (`model_name`,`year_month`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;


-- ----------------------------
-- Records of t_total
-- ----------------------------
INSERT INTO `t_total` VALUES ('000', '2012-01', '2000');
INSERT INTO `t_total` VALUES ('000', '2012-02', '2820');
INSERT INTO `t_total` VALUES ('111', '2012-01', '5000');
INSERT INTO `t_total` VALUES ('111', '2012-02', '6000');

2、建表`t_detail`

-- ----------------------------
-- Table structure for `t_detail`
-- ----------------------------
DROP TABLE IF EXISTS `t_detail`;
CREATE TABLE `t_detail` (
  `model_name` varchar(20) NOT NULL,
  `year_month` varchar(10) NOT NULL,
  `day_seq` varchar(2) NOT NULL,
  `count` int(8) NOT NULL,
  PRIMARY KEY (`model_name`,`year_month`,`day_seq`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;


-- ----------------------------
-- Records of t_detail
-- ----------------------------
INSERT INTO `t_detail` VALUES ('000', '2012-01', '1', '50');
INSERT INTO `t_detail` VALUES ('000', '2012-01', '2', '30');
INSERT INTO `t_detail` VALUES ('000', '2012-01', '3', '40');
INSERT INTO `t_detail` VALUES ('000', '2012-02', '1', '60');
INSERT INTO `t_detail` VALUES ('000', '2012-02', '2', '50');
INSERT INTO `t_detail` VALUES ('000', '2012-02', '3', '70');

3、编写SQL语句并执行

update
#select t.model_name, t.`year_month`, t.total, hd.he, t.total-hd.he jian from 
t_total t, 
(
select d.model_name, d.`year_month`, d.day_seq, d.count, sum(d.count) he
from t_detail d
group by d.model_name, d.`year_month`) hd
set t.total=t.total-hd.he
where t.model_name=hd.model_name
and t.`year_month`=hd.`year_month`