CODEWITHSUNDEEP
mysqlsql
Norman
Norman

Reputation: 6365

Update a table with count() from another table

Table test_a

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

Table 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 |

My 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 */

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.

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?

Expected results

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

Upvotes: 2

Views: 112

Answers (2)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

You can use a query for this like this

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)
)

Upvotes: 3

Devansh
Devansh

Reputation: 1267

Try this

update test_a set 
alcohol_spirits=(
select alcohol_spirits+count(*) from test_b where headDestinations like '%'+country +'%')

this query will added current alcohol_spirits with every execution you shout alwase update with only count like following query

update test_a set 
alcohol_spirits=(
select count(*) from test_b where headDestinations like '%'+country +'%')

Upvotes: 1

Related Questions