使用另一个表中的 count() 更新表 [英] Update a table with count() from another table

问题描述

表 test_a

| genId | country | alcohol_spirits | music |
|-------|---------|-----------------|-------|
|     1 |      US |               0 |     0 |
|     2 |      IN |               0 |     0 |
|     3 |      SE |               0 |     0 |

表 test_b

| itemId |       headAlias | headDestinations |   iTitle |
|--------|-----------------|------------------|----------|
|      1 | alcohol-spirits |            US,SE | Bottle 1 |
|      2 | alcohol-spirits |            US,SE | Bottle 2 |
|      3 | alcohol-spirits |            US,SE | Bottle 3 |
|      4 | alcohol-spirits |               US | Bottle 4 | 

我的sql

update test_a set alcohol_spirits = alcohol_spirits + 
(
    select 
        count(itemId) 
    from test_b 
    where headAlias = 'alcohol-spirits' 
    and headDestinations IN ('US,SE') /* 'US,SE' = user input*/

) where country IN('US,SE') ; /* 'US,SE' = user input */

我正在尝试使用来自每个国家/地区的 test_b 项目的 count() 更新表 test_a.这很难解释，但你会从我的预期结果中看到.

I'm trying to update table test_a with the count() of items from test_b for each country. It's hard to explain, but you'll see from my expected results.

对于alcohol_spirits，US 有4，SE 有3.我正在尝试一次更新所有内容，但我认为可行的方法并没有.我哪里出错了，如何正确处理?

For alcohol_spirits, the US has 4 and SE has 3. I'm trying to update it all at once, but what I thought would work, does not. Where am I going wrong and how to get this right?

预期结果

| genId | country | alcohol_spirits | music |
|-------|---------|-----------------|-------|
|     1 |      US |               4 |     0 |
|     2 |      UK |               0 |     0 |
|     3 |      SE |               4 |     0 |

推荐答案

您可以像这样使用查询

UPDATE table_a a
SET a.alcohol_spirits = a.alcohol_spirits + 
(SELECT
     count(table_b.itemId)
 FROM table_b
 WHERE headAlias = 'alcohol-spirits' 
 AND country IN('US,SE')
 AND FIND_IN_SET(a.country, table_b.headdestinations)
)

这篇关于使用另一个表中的 count() 更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！