Here the two tables are given,
Account Table:
| Client_ID | AccType | Balance |
|---|---|---|
| C100111001 | C | 5000.00 |
| C100111001 | S | 7281.1068 |
| C100111002 | C | 12300.00 |
| C100111003 | C | 1200.00 |
| C100111003 | V | 5000.00 |
| C100112001 | C | 7600.00 |
| C100113002 | C | 1300.00 |
| C100113003 | C | 4500.00 |
Transactions Table:
| Client_ID | AccType | TransType | Amount |
|---|---|---|---|
| C100111001 | C | R | 60.00 |
| C100111001 | C | D | 800.00 |
| C100111001 | S | D | 300.00 |
| C100111001 | S | R | 100.00 |
| C100111002 | C | D | 1000.00 |
| C100111002 | C | R | 40.00 |
| C100111002 | C | D | 500.00 |
| C100111002 | C | R | 80.00 |
| C100111003 | C | D | 1000.00 |
| C100111003 | C | R | 100.00 |
| C100111003 | C | D | 100.00 |
| C100111003 | V | R | 100.00 |
| C100111003 | V | D | 70.00 |
| C100111003 | V | D | 30.00 |
| C100113002 | C | D | 1000.00 |
Question is to write a query that updates the balance of all client accounts (Client_ID) of the same Account Type (AccType) in the Account Table from the transactions (Transactions Table) performed on these accounts. In the Transactions Table, we have two types of transactions 'D' and 'R', the TransType 'D' refers to Debit and 'R' refers to Credit.
For example in the first row for both Account and Transactions Table, the Client_ID C100111001 whose Acctype is 'C', the Balance should be updated as 5000+60 as the Amount in Transactions Table is of TransType='R' which means credit. Similarly, for 'D' we will do subtraction in Balance.
I am trying to write an update query by using UPDATE along with JOIN and CASES for 'R' and 'D' but I am unable to do so successfully. Please help
CASE TransType WHEN 'C' THEN Balance + Amount WHEN 'D' THEN Balance - Amount END– Barmar