Im designing a database for a bank where i have an accounts table and a transactions table shown below:
CREATE TABLE accounts
(
account INTEGER NOT NULL,
account_type ENUM('single','corporation') NOT NULL,
last_trans_date DATE NOT NULL,
minimum_balance DECIMAL(10,2) NOT NULL,
balance DECIMAL(10,2) NOT NULL,
PRIMARY KEY (account)
);
CREATE TABLE transactions
(
tran_account_number INTEGER NOT NULL,
trans_date DATE NOT NULL,
tran_amount DECIMAL(10,2) NOT NULL,
FOREIGN KEY (tran_account_number) REFERENCES accounts (account)
);
What i have to do is read in transactin table and update the accounts table without using the UPDATE statement. I have tried the function CREATE TABLE newaccount AS SELECT and then using variables to work out the new balance but the table heading will become say @newbalance:=accounts.balance = tran_amount and you cant have this. Below is code i have written that works but has these weird table headings. Please someone help me.
SELECT accounts.account,accounts.account_type,current_date(),
accounts.minimum_balance,
accounts.balance:=accounts.balance + transactions.tran_amount
FROM accounts,transactions
WHERE tran_account_number = accounts.account;
And i tried this aswell below but still the table heading came up as @:=accounts.balance + transactions.tran_amount and i want the tuple to be called Balance with all the new balances worked out.
i have attached my texts files that have all the necesary data if you wish to have a look and try. You will find the processing bit in prcesstrans.txt
CREATE TABLE accounts1 SELECT accounts.account,accounts.account_type,
current_date(),accounts.minimum_balance,
@:=accounts.balance + transactions.tran_amount
FROM accounts,transactions
WHERE tran_account_number = accounts.account;
