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;
Create a
UNIQUE
constraint on yoursubs_email
column, if one does not already exist:ALTER TABLE subs ADD UNIQUE (subs_email)
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
- 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';