SQL - IF EXISTS UPDATE ELSE INSERT INTO
196,358
Solution 1
-
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.
Solution 2
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';
Author by
Laurence Cooper
Full Stack Web & Mobile Developer based in Ibiza, Spain.
Updated on July 08, 2022Comments
-
Laurence Cooper almost 2 years
What I'm trying to do is
INSERT
subscribers in my database, butIF EXISTS
it shouldUPDATE
the row,ELSE INSERT INTO
a new row.Ofcourse I connect to the database first and
GET
the$name
,$email
and$birthday
from the url string.$con=mysqli_connect("localhost","---","---","---"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } $name=$_GET['name']; $email=$_GET['email']; $birthday=$_GET['birthday'];
This works, but just adds the new row;
mysqli_query($con,"INSERT INTO subs (subs_name, subs_email, subs_birthday) VALUES ('$name', '$email', '$birthday')"); mysqli_close($con);
Here's what I tried;
mysqli_query($con,"INSERT INTO subs (subs_name, subs_email, subs_birthday) VALUES '$name', '$email', '$birthday' ON DUPLICATE KEY UPDATE subs_name = VALUES($name), subs_birthday = VALUES($birthday)"); mysqli_close($con);
and
mysqli_query($con,"IF EXISTS (SELECT * FROM subs WHERE subs_email='$email') UPDATE subs SET subs_name='$name', subs_birthday='$birthday' WHERE subs_email='$email' ELSE INSERT INTO subs (subs_name, subs_email, subs_birthday) VALUES ('$name', '$email', '$birthday')"); mysqli_close($con);
and
mysqli_query($con,"IF NOT EXISTS(SELECT * FROM subs WHERE subs_email='$email') Begin INSERT INTO subs (subs_name, subs_email, subs_birthday) VALUES ('$name', '$email', '$birthday') End"); mysqli_close($con);
But none of them work, what am I doing wrong?
Any help is greatly appreciated!