How to create temp table with select query update it and the insert it in mysql?

Question

Can anyone tell me why this doesn't work

Create Procedure LeaveUpdate()
Begin
CREATE TEMPORARY TABLE leavebal AS (SELECT * FROM tbl_leave_balance WHERE month = month(DATE_SUB(now(), INTERVAL 1 MONTH)) and year = year(DATE_SUB(now(), INTERVAL 1 MONTH)));
UPDATE leavebal SET leave_balance = 0 WHERE leave_balance < 0;
UPDATE leavebal  SET month = month(now()), year = year(now()), leaves_taken = 0, carry_forward = leave_balance, comp_off = 0;
UPDATE leavebal SET total_leaves = carry_forward + leaves_allowed + comp_off, leave_balance = carry_forward + leaves_allowed + comp_off;
INSERT INTO tbl_leave_balance (emp_id, month, year, carry_forward, leaves_allowed, comp_off, total_leaves, leaves_taken, leave_balance)
SELECT emp_id, month, year, carry_forward, leaves_allowed, comp_off, total_leaves, leaves_taken, leave_balance FROM leavebal;
End

Welcome to Stack Overflow, unfortunately your question is lacking in information. I suggest you read [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask) then come back and edit your question to give us all the required info. Without that we can't help you and your question is likely to be closed. — Styphon, Feb 24 '17 at 13:53

score 0 · Answer 1 · answered Feb 24 '17 at 13:50

You must use delimiter,try this:

DELIMITER $$

    DROP PROCEDURE IF EXISTS `LeaveUpdate` $$
Create Procedure LeaveUpdate()
Begin
CREATE TEMPORARY TABLE leavebal AS (SELECT * FROM tbl_leave_balance WHERE month = month(DATE_SUB(now(), INTERVAL 1 MONTH)) and year = year(DATE_SUB(now(), INTERVAL 1 MONTH)));
UPDATE leavebal SET leave_balance = 0 WHERE leave_balance < 0;
UPDATE leavebal  SET month = month(now()), year = year(now()), leaves_taken = 0, carry_forward = leave_balance, comp_off = 0;
UPDATE leavebal SET total_leaves = carry_forward + leaves_allowed + comp_off, leave_balance = carry_forward + leaves_allowed + comp_off;
INSERT INTO tbl_leave_balance (emp_id, month, year, carry_forward, leaves_allowed, comp_off, total_leaves, leaves_taken, leave_balance)
SELECT emp_id, month, year, carry_forward, leaves_allowed, comp_off, total_leaves, leaves_taken, leave_balance FROM leavebal;
End$$

    DELIMITER ;

How to create temp table with select query update it and the insert it in mysql?

1 Answers1