Skip to main content

SQL: increase balances by 100% and 50%

Short answer

Use a single UPDATE with CASE: WHEN balance > 10000 THEN balance * 2 ELSE balance * 1.5 END. Alternatively run the over-10000 update first, then the others.

Consider database schema: Account (acc_no, branch_name, balance) Branch (branch_name, branch_city, assets) Customer (customer_name, customer_street, customer_city) Loan (loan_no, branch_name, amount) Depositor (customer_name, acc_no) Borrower (customer_name, loan_no) Increase all accounts with balance over 10000 by 100% and others by 50%.

Increasing by 100% means new balance = balance × 2. Increasing by 50% means new balance = balance × 1.5.

Method 1: two UPDATE statements (order matters)

-- 1st: accounts with balance over 10000 get 100% increase
UPDATE Account
SET    balance = balance * 2
WHERE  balance > 10000;

-- 2nd: all other accounts get 50% increase
UPDATE Account
SET    balance = balance * 1.5
WHERE  balance <= 10000;

The order is important. If the 50% update ran first, an account such as 9000 would become 13500, which is now above 10000, and the second statement would wrongly double it again. Running the "over 10000" update first is safe: doubled accounts stay above 10000 and are not touched by the second statement.

Method 2: one statement with CASE (best, no ordering problem)

UPDATE Account
SET balance = CASE
                 WHEN balance > 10000 THEN balance * 2
                 ELSE balance * 1.5
              END;

A single UPDATE evaluates the CASE against the old value of each row, so each account is increased exactly once.

Sample (tested): 60000 → 120000, 70000 → 140000, 5000 → 7500, 9000 → 13500, 10000 → 15000 (10000 is not over 10000), 20000 → 40000.

✓ Answer checked by Claude Opus 5.5 (high effort)

Related questions

All Database Management System chapter 3 questions →