Duplicate error when update column in the same table I am getting error code Error Code: 1062 Duplicate Entry for KEY Primary.

The Procedure is suppose to increment workshift by 1 day each week. Ex, 6001 is Monday shift, 6002 Tuesday and so on each time it's called.

I know i'm getting the error due to as soon as 6001 == 6002 in first if , then there is 2 6002 with same PK. Is there anyway to fix this?

Here is current table.

roster
 employee  branch  workShift
   0001      01       6001
   0001      01       6002
   0001      01       6003
   0001      01       6004

Table after call to update roster:

roster
 employee  branch  workShift
   0001      01       6002
   0001      01       6003
   0001      01       6004
   0001      01       6005

CREATE PROCEDURE `UpdateRoster`(in employeeid char(8), in BranchID int(11))
BEGIN
 DECLARE hours_max INT(35);
 DECLARE fin INT DEFAULT 0;
 DECLARE empid CHAR(8);
 DECLARE brnid INT(11);
 DECLARE workshfid INT(11);
 DECLARE emp INT;

 DECLARE employee_roster CURSOR FOR
         SELECT EmployeeID, BranchID, WorkingShiftID
         FROM DutyRoster;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = 1;

 OPEN employee_roster;
 REPEAT
    FETCH employee_roster INTO empid, brnid, workshfid;
        IF workshfid = '6001' THEN
            SET workshfid = '6002';

        ELSEIF workshfid = '6002' THEN
            SET workshfid = '6003';

        ELSEIF workshfid = '6003' THEN
            SET workshfid = '6005';

        ELSEIF workshfid = '6005' THEN
            SET workshfid = '6007';

        ELSEIF workshfid = '6007' THEN
            SET workshfid = '6008';

        ELSE 
            SET workshfid = '6001';
        END IF;

    UPDATE DutyRoster
    SET WorkingShiftID = workshfid;

  UNTIL fin
  END REPEAT;
  CLOSE employee_roster;
END
It appears that WorkingShiftID is the primary key of the DutyRoster table, and that you're trying to set this to the same value in all rows:

UPDATE DutyRoster SET WorkingShiftID = workshfid;


Presumably, the problem is you forgot to include a WHERE clause in that statement, so the update could be restricted to a single row.