I have a transaction table of which has the fields.
transactionID, StudentID,transactionType, transactionDate, Amount.
transactionId is the primary key.
this table has about 23,000 rows

There is also a table tblstudent in which all the students are listed with primary key StudentID.

Now I need to add an additional field "balance" to this table.
and the balance field needs to be populated against each row so that we get a sum(Amount) for each transactionID.

I have tried to do something like pseudocode

UPDATE tbltransaction
SET (balance= (SELECT SUM(balance) WHERE transactiondate< currentTransactionDate AND studentID= currentStudentID )
WHERE transactionID is from 1 to 23000.

I am using MySQL

Please help me with the SQL. Thank you very much in advance.

one, could you do a SHOW CREATE TABLE for the student table table please

two, why do you have a WHERE clause

1.
CREATE TABLE IF NOT EXISTS `tblstudent` (
`ID` int(250) NOT NULL AUTO_INCREMENT,
`admissionNumber` float NOT NULL,
`studentName` varchar(250) NOT NULL,
PRIMARY KEY (`ID`)
)

CREATE TABLE IF NOT EXISTS `tbltransaction` (
`ID` bigint(255) NOT NULL AUTO_INCREMENT,
`transactionTypeID` varchar(250) NOT NULL,
`studentID` int(250) NOT NULL,
`date` datetime NOT NULL,
`amount` float NOT NULL,
`balance` double NOT NULL,
PRIMARY KEY (`ID`)
)
2.
It is not needed

i don't understand what value you want the balance column to have

This is some thing like a bank account.
And the total balance for customer as a sum account in a particular studentID.

But when the transactions increased the need to put a balance field was necessary. This helps, since from a latest single record, we get the current Balance of the studentID.