mysql用单​​个sql更新多个表以获得sum(qty)

Question

我有两个包含数据的表

CREATE TABLE `MASTER` (
  `NAME` VARCHAR(10)      NOT NULL,
  `QTY`  INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`NAME`)
);
NAME  | QTY
----------
'ABC' | 0
'XYZ' | 0
CREATE TABLE `DETAIL` (
  `NAME` VARCHAR(10)         NOT NULL,
  `QTY`  INT(10) UNSIGNED    NOT NULL,
  `FLAG` TINYINT(1) UNSIGNED NOT NULL
);
NAME  | QTY| FLAG
--------------------
'ABC' | 10 | 0
'ABC' | 20 | 0
'PQR' | 15 | 0
'PQR' | 25 | 0

我想将 sum(detail.qty) 更新为 master 并将其标志设置为 1所以我写了查询

UPDATE MASTER M, DETAIL D
SET M.QTY = M.QTY + D.QTY,
  D.FLAG =1
WHERE M.NAME = D.NAME;

我从详细信息表中猜测 MASTER.QTY 应该是 30 (10 + 20)。但它只更新第一个值实际值为 MASTER.QTY =10(仅更新表中的第一个值)

如何获得 MASTER.QTY =30？

Answer 1

尝试这个查询:

update `MASTER` m,`DETAIL` d,
(
   SELECT `NAME`, SUM( `QTY` ) as `QTY`
   FROM `DETAIL`
   GROUP BY `NAME`
) s
SET m.QTY = s.QTY,
    d.FLAG = 1
WHERE
    m.NAME = s.NAME
    AND m.NAME = d.NAME
;

SQLFiddle 演示 --> http://www.sqlfiddle.com/#!2/ab355/1