title: MySql Event Scheduler Copy and Insert at Specific Time Every Day
body: <p>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.</p>
  
  <p>This is what I have but its not working:  </p>
  
  <pre><code>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 ;
  </code></pre>
  
  <p>For clarification, I have a table called <strong>Customer</strong> with a column named "<strong>id</strong>" that contains Unique id for all customers.</p>
  
  <p>Now I have several tables with names as <strong>Passbook.id</strong> (e.g Passbook2, Passbook3, etc...) where "<strong>id</strong>" in the Column names corresponds to Unique "<strong>id</strong>" in Table "<strong>Customer</strong>". Each <strong>Passbook.id</strong> has Columns named <strong>transactiondate, credit, debit, amount.</strong></p>
  
  <p>And I also have several tables with names as <strong>StatementofAccountRef.id</strong> where "<strong>id</strong>" in the Column names also corresponds to Unique "<strong>id</strong>" in Table "Customer". Each <strong>StatementofAccountRef.id</strong> has Columns named <strong>tid, entrydate, dtdebit, dtcredit, dtbalance.</strong></p>
  
  <p>What I want to do is to:
  1. Take each "id" from Table Customer and use it to SELECT each customer's passbook.id </p>
  
  <ol start="2">
  <li><p>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</p></li>
  <li><p>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 .</p></li>
  </ol>
  
  <p>Any working help appreciated guys, please. Thanks.</p>
  
  <p>Show Table Data and Sample entries:</p>
  
  <p>Table customer</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Table passbook63</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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</p>
  
  <p>Table StatementofAccountRef63</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Sample Data:  011, 2018-02-28 06:06:06, 36000, 36000, 96000</p>