Agfdhyk

please I need the correct Syntax to Copy and Update A table everyday from another table's MAX row values using MySql's Event Scheduler. The tasks obviously exceeds my basic knowledge of MySql.

This is what I have but its not working:

DELIMITER $$

CREATE 

    EVENT  IF NOT EXISTS `Statement_Opening_Closing_Bal` 

    ON SCHEDULE EVERY 1 DAY STARTS '00:00:30' 

    DO BEGIN



        -- GET CUSTOMER UNIQUE IDs 

        SELECT id

        FROM customer AS Customer_UniqueIDs;



        -- copy associated audit records

        SELECT transactiondate, credit, debit, amount FROM passbook.Customer_UniqueIDs WHERE transactionid=MAX(transactionid) AS  LastTransactionEachDay;



        -- INSERT MAX ROW TRANSACTION FOR ABOVE SELECT INTO StatementofAccountRef.Customer_UniqueIDs,

        INSERT INTO StatementofAccountRef.Customer_UniqueIDs (tid, entrydate, dtdebit, dtcredit, dtbalance)

       VALUES(NULL, LastTransactionEachDay.entrydate, LastTransactionEachDay.dtdebit, LastTransactionEachDay.dtcredit, LastTransactionEachDay.dtbalance)





    END */$$



DELIMITER ;

For clarification, I have a table called Customer with a column named "id" that contains Unique id for all customers.

Now I have several tables with names as Passbook.id (e.g Passbook2, Passbook3, etc...) where "id" in the Column names corresponds to Unique "id" in Table "Customer". Each Passbook.id has Columns named transactiondate, credit, debit, amount.

And I also have several tables with names as StatementofAccountRef.id where "id" in the Column names also corresponds to Unique "id" in Table "Customer". Each StatementofAccountRef.id has Columns named tid, entrydate, dtdebit, dtcredit, dtbalance.

What I want to do is to:
1. Take each "id" from Table Customer and use it to SELECT each customer's passbook.id

2. Get the Max row values from each passbook.id for Columns "transactiondate", "debit", "credit", "amount". By Max Row values I mean last or most recent row entry in passbook.id as at the time the Event Scheduler runs. This value will be picked at the specific time (00.00.30) everyday whether they change or not as far as they are the most recent entry in table passbook.id




3. Insert the values into each corresponding StatementofAccountRef.id Column entrydate, dtdebit, dtcredit, dtbalance. "tid" column in StatementofAccountRef.id tables is set to AUTO INCREMENT. entrydate(in StatementofAccountRef.id) is DATETIME as is transactiondate in passbook.id .

Any working help appreciated guys, please. Thanks.

Show Table Data and Sample entries:

Table customer

CREATE TABLE `customer` (

 `id` int(5) NOT NULL,

 `name` varchar(255) NOT NULL,

 `surname` varchar(255) DEFAULT NULL,

 `gender` char(1) NOT NULL,

 `dob` date NOT NULL,

 `nominee` varchar(255) NOT NULL,

 `account` varchar(255) NOT NULL,

 `address` varchar(255) NOT NULL,

 `mobile` varchar(15) NOT NULL,

 `email` varchar(255) NOT NULL,

 `accessid` varchar(60) NOT NULL,

 `password` varchar(255) NOT NULL,

 `branch` varchar(255) NOT NULL,

 `Branch_Code` varchar(255) NOT NULL,

 `lastlogin` datetime NOT NULL,

 `accstatus` varchar(255) NOT NULL,

 `accnumber` bigint(10) DEFAULT NULL,

 `CustomerRefNum` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

 `CustomerTokenRef` varchar(255) DEFAULT NULL,

 `Acc_Manager` varchar(255) DEFAULT NULL,

 `currency` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1



63, John, Denon, M, 1976-12-10, Jonny Lee, GoldPlus, 200 Monroe Street #233 Rockville MD 2085, cmailultimate@gmail.com, 0458ac7536f4101f597820c7f9136b2354f2f156, Zone C, Team 1, 2018-11-18 03-14-45, Active, 12113, CUDG23299-TYWB02323, Raymond Crow, Dollars

Table passbook63

CREATE TABLE `passbook63` (

 `transactionid` int(5) NOT NULL,

 `transactiondate` date DEFAULT NULL,

 `name` varchar(255) DEFAULT NULL,

 `surname` varchar(255) DEFAULT NULL,

 `fullnames` varchar(255) DEFAULT NULL,

 `branch` varchar(255) DEFAULT NULL,

 `Branch_Code` varchar(255) DEFAULT NULL,

 `credit` int(10) DEFAULT NULL,

 `debit` int(10) DEFAULT NULL,

 `amount` float(10,2) DEFAULT NULL,

 `narration` varchar(255) DEFAULT NULL,

 `recall_value` varchar(10) DEFAULT NULL,

 `transactionRef` varchar(255) DEFAULT NULL,

 `transactionreceipts` varchar(255) DEFAULT NULL,

 `receiving_Inst` varchar(255) DEFAULT NULL,

 `beneficiary_account` varchar(255) DEFAULT NULL,

 `beneficiary_name` varchar(255) DEFAULT NULL,

 `transaction_datetime` datetime NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

Sample Data: 19, 2017-10-15 06:06:06, John, Denon, John Denon, Zone C, Team 1, 6000, 6000, 6000, Double Bet On Customer 101, 0, WERW39489923, Triple Confirmation of Results Reguired, 12113, Harrison Due, 2017-10-19 01:06:06

Table StatementofAccountRef63

CREATE TABLE `StatementofAccountRef63` (

 `tid` int(11) NOT NULL AUTO_INCREMENT,

 `entrydate` datetime DEFAULT NULL,

 `dtdebit` int(11) NOT NULL,

 `dtcredit` int(11) NOT NULL,

 `dtbalance` int(11) NOT NULL,

 PRIMARY KEY (`tid`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

Sample Data: 011, 2018-02-28 06:06:06, 36000, 36000, 96000