SQL - IF EXISTS UPDATE ELSE INSERT INTO

INSERT ... ON DUPLICATE KEY UPDATE

is a good solution as long as you don't mind AUTO_INCREMENT counters unnecessarily incrementing every time you end up doing an UPDATE. Since it tries to INSERT first, I noticed auto counters do increment. Another solution I like that may be less performant, but easy to maintain is:

IF EXISTS(SELECT 1 FROM table WHERE column = value...) THEN
    UPDATE table 
    SET column = value ...
    WHERE other_column = other_value ...;
ELSE
    INSERT INTO table
        (column1, column2, ...)
    VALUES
        (value1, value2, ...);
END IF;

  1. Create a UNIQUE constraint on your subs_email column, if one does not already exist:

    ALTER TABLE subs ADD UNIQUE (subs_email)
    
  2. Use INSERT ... ON DUPLICATE KEY UPDATE:

    INSERT INTO subs
      (subs_name, subs_email, subs_birthday)
    VALUES
      (?, ?, ?)
    ON DUPLICATE KEY UPDATE
      subs_name     = VALUES(subs_name),
      subs_birthday = VALUES(subs_birthday)
    

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE - dev.mysql.com

  1. Note that I have used parameter placeholders in the place of string literals, as one really should be using parameterised statements to defend against SQL injection attacks.

Try this:

INSERT INTO `center_course_fee` (`fk_course_id`,`fk_center_code`,`course_fee`) VALUES ('69', '4920153', '6000') ON DUPLICATE KEY UPDATE `course_fee` = '6000';