mySQL transaction from on table to another with procedure

user_15661749 0
Viewed 27

I need to create a procedure with a transaction that takes employeeID and AppointmentID as parameter and then transfers all information from appointment table where appointmentID param matches the appointmentID in the appointment table to the completed_doses table and additionally insert the employeeID param in the same row.

this is my code so far:

enter code here
DELIMITER //
create procedure appointment_finished (in employeeID  mediumint, in appointmentID int(10)) 
begin 
declare vSQLSTATE char(5) default '00000';
declare continue handler for sqlexception
begin 
get diagnostics condition 1
vSQLSTATE = returned_SQLSTATE;
end;
start transaction;
insert into completed_doses 
select
(select *
from appointment
where appointmentID = appointment.appointmentID),
(employeeID);
select vSQLSTATE;
if vSQLSTATE = '00000' then commit;
else rollback;
end if;
end //
DELIMITER ;
procedure transactions mysql
1 Answers

assuming that completed_doses table has the same layout as appointment plus the column employeeID at the end; and assuming that you need to include the employee id in the same row then:

this bit of code

insert into completed_doses 
select
(select *
from appointment
where appointmentID = appointment.appointmentID),
(employeeID)

should be

insert into completed_doses 
select *,employeeID 
from appointment
where appointmentID = appointment.appointmentID
user_29649630
Related