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?
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'