How can I update value with the conditions of sum()?

November 23, 2021

I have a tables(following bank schemas) below :

//account
+----------------+---------+
| account_number | balance |
+----------------+---------+
| 11_01          |     309 |
| 11_02          |     515 |
| 12_01          |    1050 |
| 13_01          |  105000 |
| 13_02          |    1000 |
| 14_01          |    6300 |
| 17_01          |   10500 |
+----------------+---------+

//customer
+-------------+---------------+--------------------+---------------+---------------+
| customer_id | customer_name | customer_street    | customer_city | customer_type |
+-------------+---------------+--------------------+---------------+---------------+
| abc         | 이승종        | 송파구 방이동 28-2 | 서울          | NULL          |
| def         | 이규현        | 남구 달동 1251-5   | 울산          | NULL          |
| ghi         | 이명구        | 동래구 수안동 2-5  | 부산          | NULL          |
| jkl         | 신영찬        | 신곡동 357         | 의정부        | NULL          |
| mno         | 김민석        | 신장동 444-25      | 하남          | NULL          |
| pqr         | 박승원        | 북구 삼각동 592-1  | 광주          | NULL          |
| stu         | 정창현        | 서구 청라동 156-1  | 인천          | NULL          |
+-------------+---------------+--------------------+---------------+---------------+

//depositor
+-------------+----------------+-------------+
| customer_id | account_number | access_date |
+-------------+----------------+-------------+
| abc         | 11_01          | 2021-11-12  |
| abc         | 11_02          | 2021-11-12  |
| def         | 12_01          | 2021-11-12  |
| ghi         | 13_01          | 2021-11-12  |
| ghi         | 13_02          | 2021-11-12  |
| jkl         | 14_01          | 2021-11-12  |
| stu         | 17_01          | 2021-11-12  |
+-------------+----------------+-------------+

sorry for korean language, but don’t care about that.

I want to update customer table’s column customer_type value to ‘VIP’ with the conditions of sum of each customer’s account’s balance is over 10000.

I tried inner join -> group by(customer_name) clause, but it doesn’t work well.
What clause should I use?

>Solution :

You could try using an update in join with the subquery for sum

update customer c
inner join  (
    select d.customer_id, sum(a.balance) cust_sum
    from depositor d 
    inner join account a on a.account_number = d.account_number 
    group by d.customer_id
) t on t.customer_id = c.customer_id 
        AND t.cust:sum > 10000
set customer_type= 'VIP'