title: Update using a procedure in MySQL
body: <p>I've spent all day trying to get this to work.</p>
  
  <p>I have a procedure that performs calculations on dates based on an occurrence type. It returns two new datetime fields from a SELECT.</p>
  
  <p>I want to use this procedure to update an events table. Something like this.</p>
  
  <pre><code>UPDATE
      events
  SET 
      start = new_start,
      end = new_end
  FROM CALL updateEvents(events.occurance, events.day_num, start, end)
  WHERE
      end &lt; NOW();
  </code></pre>
  
  <p>I'm using MySQL workbench but it reports that 'FROM' is not valid input at this position.</p>
  
  <p>Here is the procedure.</p>
  
  <pre><code>CREATE DEFINER=`admin`@`%` PROCEDURE `updateEvents`(occurance VARCHAR(20), day_num INT, start_time DATETIME, end_time DATETIME)
  BEGIN
      DECLARE time_interval INT;
      DECLARE new_start DATETIME;
      DECLARE last_day DATETIME;
      DECLARE last_hours INT;
  
      SET time_interval = UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time);
      SET last_hours = UNIX_TIMESTAMP(start_time) - UNIX_TIMESTAMP(DATE(start_time));
      SET last_day = FROM_UNIXTIME(UNIX_TIMESTAMP(LAST_DAY(start_time)) + last_hours, "%Y-%m-%d %H:%i:%s");
      SET new_start =
          CASE occurance
          WHEN 'daily' THEN DATE_ADD(start_time, INTERVAL 1 DAY)
          WHEN 'weekly' THEN DATE_ADD(start_time, INTERVAL 1 WEEK)
          WHEN 'monthly' THEN DATE_ADD(start_time, INTERVAL 1 MONTH)
          WHEN 'bimonthly' THEN DATE_ADD(start_time, INTERVAL 2 MONTH)
          WHEN 'quarterly' THEN DATE_ADD(start_time, INTERVAL 1 QUARTER)
          WHEN 'firstof' THEN
              CASE
                  WHEN DATE_ADD(DATE_FORMAT(start_time, '%Y-%m-01 %H:$i:$s'), INTERVAL (7+day_num - WEEKDAY(DATE_FORMAT(start_time, '%Y-%m-01 %H:$i:$s')))%7 DAY) &gt; start_time
                  THEN DATE_ADD(DATE_FORMAT(start_time, '%Y-%m-01 %H:$i:$s'), INTERVAL (7+day_num - WEEKDAY(DATE_FORMAT(start_time, '%Y-%m-01 %H:$i:$s')))%7 DAY)
                  ELSE DATE_ADD(DATE_FORMAT(DATE_ADD(start_time, INTERVAL 1 MONTH), '%Y-%m-01 %H:$i:$s'), INTERVAL (7+day_num - WEEKDAY(DATE_FORMAT(DATE_ADD(start_time, INTERVAL 1 MONTH), '%Y-%m-01 %H:$i:$s')))%7 DAY)
              END
          WHEN 'secondof' THEN
              CASE
                  WHEN DATE_ADD(DATE_FORMAT(start_time, '%Y-%m-01 %H:$i:$s'), INTERVAL (7+day_num - WEEKDAY(DATE_FORMAT(start_time, '%Y-%m-01 %H:$i:$s')))%14 DAY) &gt; start_time
                  THEN DATE_ADD(DATE_FORMAT(start_time, '%Y-%m-01 %H:$i:$s'), INTERVAL (7+day_num - WEEKDAY(DATE_FORMAT(start_time, '%Y-%m-01 %H:$i:$s')))%14 DAY)
                  ELSE DATE_ADD(DATE_FORMAT(DATE_ADD(start_time, INTERVAL 1 MONTH), '%Y-%m-01 %H:$i:$s'), INTERVAL (7+day_num - WEEKDAY(DATE_FORMAT(DATE_ADD(start_time, INTERVAL 1 MONTH), '%Y-%m-01 %H:$i:$s')))%14 DAY)
              END
          WHEN 'lastof' THEN
              CASE 
                  WHEN DATE_SUB(last_day, INTERVAL ((WEEKDAY(last_day)+7-day_num))%7 DAY) &gt; start_time
                  THEN DATE_SUB(last_day, INTERVAL ((WEEKDAY(last_day)+7-day_num))%7 DAY)
                  ELSE DATE_SUB(DATE_ADD(last_day, INTERVAL 1 MONTH), INTERVAL ((WEEKDAY(DATE_ADD(last_day, INTERVAL 1 MONTH))+7-day_num))%7 DAY)
              END
      ELSE
          start_time
      END;
      SELECT
          new_start,
          FROM_UNIXTIME(UNIX_TIMESTAMP(new_start) + time_interval,"%Y-%m-%d %H:%i:%s") AS new_end;
  END
  </code></pre>
  
  <p>PS. excuse my mis-spelling of occurrence.</p>