mysql - 根据在多个表中找到的列的总和更新表值的查询

Question

我正在尝试编写一个查询来对来自不同表的所有 prc 值求和并将其更新到 main_trans.tot。

例如。 TR01 的 tot 值应为 30 添加两个表中的所有 TR01 prc。

表 main_trans:

| id | tot | 
|TR01|  30 |
|TR02|  5  |

表 sub_trans_a:

| id | prc |
|TR01|  10 |
|TR01|  10 |

表 sub_trans_b:

| id | prc |
|TR01|  10 |
|TR02|  5  |

我不知道如何编写它才能根据行的 ID 自动更新所有行。到目前为止，如果我专门写入 id 列的值，我的查询就可以完成工作:

UPDATE main_trans  SET tot =  
(SELECT SUM(prc) FROM sub_trans_a WHERE id = 'TR01')
+ (SELECT SUM(prc) FROM sub_trans_b WHERE id = 'TR01') 
WHERE id = 'TR01'

Answer 1

您可以在带有联合集的更新查询中使用联接

UPDATE main_trans m  
join
(SELECT id,SUM(prc) prc
FROM (
SELECT id,SUM(prc) prc FROM sub_trans_a WHERE id = 'TR01'
union all
SELECT id,SUM(prc) prc FROM sub_trans_b WHERE id = 'TR01'
) t1
) t
on(t.id = m.id)
SET m.tot = t.prc 
WHERE m.id = 'TR01'

此外，如果您对 sub_trans_a 和 sub_trans_a 具有相同的结构，那么为什么要有 2 个表，为什么不只是一个表或一个列作为 type a 或 type b

See Demo

或者，如果您想在不提供 id 值的情况下更新整个 main_trans 表，您可以通过在查询中添加分组依据来实现

UPDATE main_trans m  
join
(SELECT id,SUM(prc) prc
FROM (
SELECT id,SUM(prc) prc FROM sub_trans_a group by id
union all
SELECT id,SUM(prc) prc FROM sub_trans_b group by id
) t1  group by id
) t
on(t.id = m.id)
SET m.tot = t.prc 

See Demo 2

编辑的好建议Andomar 您可以将内部查询简化为

UPDATE main_trans m  
join
(SELECT id,SUM(prc) prc
FROM (
SELECT id,prc FROM sub_trans_a
union all
SELECT id,prc FROM sub_trans_b 
) t1 WHERE id = 'TR01'
) t
on(t.id = m.id)
SET m.tot = t.prc 
WHERE m.id = 'TR01'