Wsrtjtyk

I'm using the following stored-procedure to update a table:

DELIMITER $$

CREATE DEFINER=`developer`@`localhost` PROCEDURE `update_patient`(IN `patient_id` INT(11), IN `name` VARCHAR(45), IN `surname` VARCHAR(45), IN `middle_name` VARCHAR(45), IN `email` VARCHAR(45), IN `phone` VARCHAR(45), IN `mobile` VARCHAR(45), IN `address_id` INT(11), IN `address_no` VARCHAR(8), IN `ID` VARCHAR(45), IN `DOB` DATE)

    NO SQL

UPDATE

  patient

SET name  = name,

  surname = surname,

  middle_name = middle_name,

  email = email,

  phone = phone,

  mobile = mobile,

  address_id = address_id,

  address_no = address_no,

  ID = ID,

  DOB = DOB      

WHERE

  patient_id = patient_id

LIMIT 1;

END$$

DELIMITER ;

When I'm trying to call it through phpmyadmin I get the error: #1062 - Duplicate entry '844844' for key 'ID_UNIQUE'

844844 refers to ID field. I have this field in patient table and I want to update the patient's data. However, the primary key of patient table is patiend_id and not ID.

Do you know how to fix the error?

The problem is your input params for the Stored procedure are same as your column names in the table. This is leading to ambiguous behaviour.

Eg: In SET name = name ; how does MySQL resolve which one of this is the param value and which one is the column name ?

I generally prefix IN params with in_ and OUT with out_ for code readability and avoiding ambiguous behaviour.

DELIMITER $$

CREATE definer=`developer`@`localhost`

PROCEDURE `update_patient`(IN `in_patient_id`  INT(11),

                           IN `in_name`        VARCHAR(45),

                           IN `in_surname`     VARCHAR(45),

                           IN `in_middle_name` VARCHAR(45),

                           IN `in_email`       VARCHAR(45),

                           IN `in_phone`       VARCHAR(45),

                           IN `in_mobile`      VARCHAR(45),

                           IN `in_address_id`  INT(11),

                           IN `in_address_no`  VARCHAR(8),

                           IN `in_id`          VARCHAR(45),

                           IN `in_dob`         date)

  NO SQL



  UPDATE patient

  SET    name = in_name,

         surname = in_surname,

         middle_name = in_middle_name,

         email = in_email,

         phone = in_phone,

         mobile = in_mobile,

         address_id = in_address_id,

         address_no = in_address_no,

         id = in_id,

         dob = in_dob

  WHERE  patient_id = in_patient_id

  LIMIT  1;



END$$

DELIMITER ;