mysql - 使用该表本身的所选值的总和更新表

Question

我有一个表 tmp_addit 看起来像这样

addition_id   allowance_id    sal_id   amount
 --------------------------------------------
   1             4            1       300              
   2             5            1       400        
   3             6            1       200       
   4             4            2       300      
   5             5            2       250
   6             6            2       150

我想更新表格，以便 (5,6) 中具有 allowance_id 的 amount 必须添加到 allowance_id = 4。例如:我想获取 900 作为 amount for (sal_id = 1 and allowance_id = 4).

我试过这段代码，但它不起作用

 update tmp_addit set amount =(select sum(amount) from  tmp_addit  
 where allowance_id in(5,6)
 group by salary_id) where allowance_id=4;

有什么帮助吗？

Answer 1

使用此查询:

select salary_id, sum(amount) amount
from tmp_addit
where allowance_id in (5, 6)
group by salary_id

您会得到要添加到列 amount 中的总和。
然后，您需要在 UPDATE 语句中将此查询连接到表中:

update tmp_addit t
inner join (
  select salary_id, sum(amount) amount
  from tmp_addit
  where allowance_id in (5, 6)
  group by salary_id
) g on g.salary_id = t.salary_id
set t.amount = t.amount + g.amount
where t.allowance_id = 4;

参见 demo .
结果:

| addition_id | allowance_id | salary_id | amount |
| ----------- | ------------ | --------- | ------ |
| 1           | 4            | 1         | 900    |
| 2           | 5            | 1         | 400    |
| 3           | 6            | 1         | 200    |
| 4           | 4            | 2         | 700    |
| 5           | 5            | 2         | 250    |
| 6           | 6            | 2         | 150    |