SQL - IF EXISTS UPDATE ELSE INSERT INTO

Question

What I'm trying to do is INSERT subscribers in my database, but IF EXISTS it should UPDATE 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!

Possible duplicate of Insert into a MySQL table or update if exists — Jim Fell
– Jim Fell, Commented Jun 7, 2016 at 14:41

Community · Accepted Answer · 2017-05-23 12:10:34Z

242

Create a UNIQUE constraint on your subs_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.

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Mar 13, 2013 at 11:26

eggyal

127k18 gold badges221 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

22 Comments

Laurens Kuiper Over a year ago

You da Man! Works like a charm, now to find a way to protect this against injections. I'm sending the data to the database after people have confirmed their subscription to the newsletter, with the parameters posted in the string. Any idea how to secure this? Thnx

eggyal Over a year ago

@RehbanKhatri: Oh, sorry, I thought you meant in the VALUES() clause. The VALUES() function just takes the name of the column whose value from the VALUES() clause you wish to use: it's simply a shorthand to prevent you repeating yourself; you don't have to use it. You could just directly use your function in the SET clause if you want, eg SET column = MYFUNCTION().

amdev Over a year ago

Mmmh that have sense, I just read an article about that, maybe the article is wrong. percona.com/blog/2010/07/14/…

eggyal Over a year ago

@amdev: That's an interesting article, delving into some very low level details of MySQL's storage engine API which I didn't know about—so thank you for bringing me up to speed. However, I would still prefer to express my desired logic as clearly as possible in order to aid future maintainability: only if the other concerns mentioned above happen not to be relevant and it's causing a material performance impact would I consider switching to REPLACE when I really intend to update. Remember Knuth's maxim: "premature optimisation is the root of all evil".

eggyal Over a year ago

@joseph: "With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values."

|

Sabito · Accepted Answer · 2020-10-16 05:09:06Z

3

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';

edited Oct 16, 2020 at 5:09

Sabito

5,23610 gold badges39 silver badges66 bronze badges

answered Oct 16, 2020 at 4:34

Manish Kumar

511 bronze badge

2 Comments

Yehor Androsov Over a year ago

This SQL script has tables/columns unrelated to OP's schema

twhitney Over a year ago

Important: this will increase the autoincrement on every run regardless of whether it UPDATEs or INSERTs, so if you run multiple of these queries in a script, it will increase the autoincrement by n with every run-through. Kind of a deal-breaker in some situations.

Tim Carr · Accepted Answer · 2022-03-23 16:12:29Z

3

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;

answered Mar 23, 2022 at 16:12

Tim Carr

312 bronze badges

1 Comment

Lusha Li Over a year ago

For the second solution, it has to be wrapped in a procedure, right?

Collectives™ on Stack Overflow

SQL - IF EXISTS UPDATE ELSE INSERT INTO

3 Answers 3

22 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

22 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related