Abumiyan Muqri

Abumiyan Muqri

  • NA
  • 43
  • 48.3k

Insert/Update in MySql

Jul 16 2012 5:37 AM

Hi all,

I have written following procedure in MySql.I want to insert record if it is not exist in table or update it if it is already exist.
But when i call it from C# code to fetch data from XMl file, it gives error as "Cannot isert record" because in table sid is Primary Key.

Please Help...

DELIMITER $$
DROP PROCEDURE IF EXISTS `dbsims`.`SystemInfoInsert` $$
CREATE PROCEDURE `dbsims`.`SystemInfoInsert`
  (
    IN p_sid varchar(100),
    IN p_host_name varchar(20),
    IN p_ip varchar(20),
    IN p_solution varchar(50)
  )

BEGIN
   IF EXISTS(select * from system_info where sid = p_sid) THEN
     UPDATE system_info set sid=p_sid, host_name=p_host_name, ip=p_ip, solution=p_solution;
   ELSE
      INSERT INTO system_info
      (
        sid,
        host_name,
        ip,
        solution,
        last_updated_date
      )
      VALUES
      (
        p_sid,
        p_host_name,
        p_ip,
        p_solution,
        NOW()
      );
  END IF;
END $$
DELIMITER ;

Answers (2)