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

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?

CodePudding user response：

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'

CodePudding user response：

UPDATE customer
LEFT JOIN ( SELECT customer_id, SUM(balance) > 10000 is_VIP
            FROM account
            JOIN depositor USING (account_number)
            GROUP BY customer_id ) check_VIP USING (customer_id)
SET customer_type = COALESCE(is_VIP, 0)
-- WHERE customer_type IS NULL;

customer_type will be assigned to 1 if a customer is VIP, and 0 if not. If you need to set another value then apply according CASE in SET clause. For example SET customer_type = CASE WHEN is_VIP THEN 'VIP' ELSE '' END.

If this column contains a value in some rows and such rows should not be updated, uncomment WHERE.