MySql:从另一个表插入表SELECT，其中first_table ID = another_table.id

Question

我有这个起始表:

+----+-------------+---------+
| id | id_customer | balance |
+----+-------------+---------+
|  1 |         123 | NULL    |
|  2 |         124 | NULL    |
|  3 |         125 | NULL    |
|  4 |         126 | NULL    |
+----+-------------+---------+

我需要使用另一个表中的 SELECT SUM 填充该 balance 列，其中第一个表中的 id_customer = 第二个表中的 id_customer。

+----+-------------+--------+------------+
| id | id_customer | amount | date_trans |
+----+-------------+--------+------------+
|  1 |         123 |    100 | 2018-01-01 |
|  2 |         123 |    -10 | 2018-01-04 |
|  3 |         125 |     70 | 2018-01-01 |
|  4 |         124 |     10 | 1994-05-04 |
|  5 |         124 |     20 | 2018-01-01 |
|  6 |         126 |     10 | 2018-01-16 |
|  7 |         126 |     50 | 2018-04-16 |
+----+-------------+--------+------------+

第二个表的条件是 SUM(金额) WHERE date_trans BETWEEN '2010-01-01' AND '2018-02-28'。

因此，在我的示例中，我需要最终的第一个表为:

+----+-------------+---------+
| id | id_customer | balance |
+----+-------------+---------+
|  1 |         123 | 90      |
|  2 |         124 | 20      |
|  3 |         125 | 70      |
|  4 |         126 | 10      |
+----+-------------+---------+

id 2 只有 20，因为 id 4 处的 10$ 交易超出范围。id 4 只有 10，导致 id 7 处的 50$ 交易超出范围。

这是从 secondary_table 中提取 SUM 的(简单)查询。

SELECT id_customer , SUM(balance) AS saldo FROM second_table
WHERE date_trans BETWEEN '2010-01-01 00:00:00' AND '2018-02-28 23:59:59'
GROUP BY id_customer

通过此查询，我还有几个对我来说不感兴趣的 ID(~99400)，因此我需要执行类似的伪代码:

INSERT INTO first_table ( balance ) 
SELECT saldo FROM
( SELECT id_customer , SUM(balance) AS saldo FROM second_table
    WHERE date_trans BETWEEN '2010-01-01 00:00:00' AND '2018-02-28 23:59:59'
    GROUP BY id_customer )
WHERE first_table.id_customer = second_table.id_customer

我无法使用“IN”，因为第二个表大约有 100.000 行。首发表大约是600。

Answer 1

只需在 UPDATE 语句的 SET 子句中使用(相关的)子查询即可:

UPDATE first_table t1
SET t1.balance = (
  SELECT SUM(t2.amount)
  FROM second_table t2
  WHERE t2.date_trans BETWEEN '2010-01-01 00:00:00' AND '2018-02-28 23:59:59'
    AND t2.id_customer = t1.id_customer
);

演示:http://sqlfiddle.com/#!9/9e6aa2/1