Update if exist else insert not working

Question

I need help regarding a SQL procedure. I have tried and done every way I can but the below procedure didn't work, Need help why it's not working,

CREATE PROCEDURE updateUser
     (IN ID INT(11), 
      name VARCHAR(100), 
      email VARCHAR(100), 
      role VARCHAR(9), 
      gender VARCHAR(6), 
      birthday VARCHAR(10), 
      classroom INT(11) )  

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

   BEGIN TRANSACTION;
       IF EXISTS (SELECT * FROM student WHERE userID = ID)
       BEGIN
           UPDATE tbl_users 
           SET userName = name, userEmail = email, userRole = role, 
               gender = gender, birthday = birthday 
           WHERE userID = ID;

           UPDATE student 
           SET classID = classroom 
           WHERE userID = ID;
       END
       ELSE
       BEGIN
           UPDATE tbl_users 
           SET userName = name, userEmail = email, userRole = role, 
               gender = gender, birthday = birthday 
           WHERE userID = ID;

           INSERT INTO student(classID, userID) 
           VALUES (classroom, ID);
       END

       COMMIT TRANSACTION;

Above update into student query and insert into query both work individually. But not when IF EXISTS is inserted.

Answer 1

Since you are using MySQL. Try the below code and see whether it works,

CREATE PROCEDURE updateUser
     (IN ID INT(11), 
      name VARCHAR(100), 
      email VARCHAR(100), 
      role VARCHAR(9), 
      gender VARCHAR(6), 
      birthday VARCHAR(10), 
      classroom INT(11) )  


   BEGIN
       IF EXISTS (SELECT * FROM student WHERE userID = ID) THEN
       START TRANSACTION;
           UPDATE tbl_users 
           SET userName = name, userEmail = email, userRole = role, 
               gender = gender, birthday = birthday 
           WHERE userID = ID;

           UPDATE student 
           SET classID = classroom 
           WHERE userID = ID;
       COMMIT;
       ELSE
       START TRANSACTION;
           UPDATE tbl_users 
           SET userName = name, userEmail = email, userRole = role, 
               gender = gender, birthday = birthday 
           WHERE userID = ID;

           INSERT INTO student(classID, userID) 
           VALUES (classroom, ID);

       COMMIT;
       END IF;
   END;