MySQL user defined rollback procedure

Question

I'm attempting to write my own mini-rollback procedure. I have a table that tracks any updates or deletes to another table using a trigger. I am attempting to make it possible to restore one or more of these tracked changes through the use of a procedure. However, I'm receiving a syntax error with the following:

-- UNDO_CHANGES_PROCEDURE - This copies the values of the work log track table back into the relevant record in the work log table if an accidental edit or deletion is made.
DROP PROCEDURE IF EXISTS UNDO_CHANGES_PROCEDURE;

DELIMITER $$

CREATE PROCEDURE UNDO_CHANGES_PROCEDURE(ID INT(6))
BEGIN
DECLARE var_trig CHAR(8);

SET var_trig = (SELECT TRIGGER_TYPE FROM WORK_LOG_TRACK WHERE WORK_LOG_TRACK.WORK_LOG_EDIT_NUMBER = ID);

IF var_trig = 'Update' THEN
UPDATE WORK_LOG SET ENTRY_NUMBER = WORK_LOG_TRACK.ENTRY_NUMBER_FK, EMPLOYEE_ID_FK = WORK_LOG_TRACK.EMPLOYEE_ID_FK, WORK_ORDER_NUMBER_FK = WORK_LOG_TRACK.WORK_ORDER_NUMBER_FK, ENTRY_TIME = WORK_LOG_TRACK.ENTRY_TIME, WORK_DONE = WORK_LOG_TRACK.WORK_DONE WHERE WORK_LOG_TRACK.WORK_LOG_EDIT_NUMBER = ID AND WORK_LOG.ENTRY_NUMBER = WORK_LOG_TRACK.ENTRY_NUMBER_FK;
ELSE
INSERT INTO WORK_LOG(ENTRY_NUMBER, EMPLOYEE_ID_FK, WORK_ORDER_NUMBER_FK, ENTRY_TIME, WORK_DONE) VALUES (WORK_LOG_TRACK.ENTRY_NUMBER_FK, WORK_LOG_TRACK.EMPLOYEE_ID_FK, WORK_LOG_TRACK.WORK_ORDER_NUMBER_FK, WORK_LOG_TRACK.ENTRY_TIME, WORK_LOG_TRACK.WORK_DONE) WHERE WORK_LOG_TRACK.WORK_LOG_EDIT_NUMBER = ID;
END IF;
END;
$$

DELIMITER ;

The syntax error comes in in regards to my update statement, any help or suggestions would be appreciated.

Answer 1

The syntax error comes from the INSERT statement. The statement INSERT...VALUES does not support WHERE clause.

Possible you want this -

INSERT INTO WORK_LOG (
  ENTRY_NUMBER, EMPLOYEE_ID_FK, WORK_ORDER_NUMBER_FK, ENTRY_TIME, WORK_DONE
)
SELECT
  ENTRY_NUMBER_FK,
  EMPLOYEE_ID_FK,
  WORK_ORDER_NUMBER_FK,
  ENTRY_TIME,
  WORK_DONE
FROM
  WORK_LOG_TRACK
WHERE
    WORK_LOG_EDIT_NUMBER = ID;