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](http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) — Jim Fell, Jun 07 '16 at 14:41

score 239 · Accepted Answer · edited May 23 '17 at 12:10

239

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 '17 at 12:10

Community

1
1

answered Mar 13 '13 at 11:26

eggyal

122,705
18
212
237

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 – Laurence Cooper Mar 13 '13 at 13:06
@freakstyler: See my third point, especially the answer to which it links. – eggyal Mar 13 '13 at 13:10
fok this is cewl! I've been using MySQL quite a time now and never used this before! – Sam Vloeberghs Dec 20 '14 at 15:55
I just tried this and I don't have to use `VALUES()` in the `UPDATE` statement. – Mark Feb 13 '15 at 20:24
But if InnoDB Eng is used, the auto increment id will grow crazy fast. – Zagfai Jun 01 '15 at 07:42
@eggyal can we use mysql user defined function in VALUES()? – Rehban Khatri Jun 20 '16 at 06:02
@RehbanKhatri: Sure, why not? – eggyal Jun 20 '16 at 07:01
I'm not able to, It says Syntax error. So I had to declare a variable and store the function's returned value and use that variable inside VALUES() – Rehban Khatri Jun 20 '16 at 07:03
1

@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()`. – eggyal Jun 20 '16 at 07:07
Why don't use "REPLACE INTO" instead ( or merge into for db2 ) ? it seems easier and faster. – amdev Aug 10 '16 at 13:08
@amdev: `REPLACE` deletes the old record (with any FK and trigger implications), then inserts a new one (again with side effects); moreover, because the new record is specified by the values given in the `REPLACE` statement, any omitted columns will be set to their default values (whereas the above updates only the specified columns, leaving everything else untouched). Not sure where you got the idea that `REPLACE` is faster either, since an update should always outperform delete & insert. As to easier, `REPLACE` still requires a unique constraint to be defined... – eggyal Aug 10 '16 at 13:09
1

Mmmh that have sense, I just read an article about that, maybe the article is wrong. https://www.percona.com/blog/2010/07/14/why-insert-on-duplicate-key-update-may-be-slow-by-incurring-disk-seeks/ – amdev Aug 10 '16 at 13:11
And what about the MERGE INTO which seems proceed a real update or new insert ? good example here, http://stackoverflow.com/questions/15701327/db2-equivalent-of-mysql-replace-into – amdev Aug 10 '16 at 13:14
1

@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 Aug 10 '16 at 13:18
@amdev: As for `MERGE INTO`—this question was about MySQL, which doesn't support that command. Its performance on other systems will be implementation dependent. – eggyal Aug 10 '16 at 13:19
GIVES ME AN ERROR! – Ramesh Pareek Feb 10 '17 at 15:58
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''AJAY KUMAR')' at line 2 The SQL being executed was: INSERT INTO fee_acc_balance (guardian_name, account_no, paid_amount, due, days_overdue, total_fees, updated_on) VALUES ('AJAY KUMAR', '10', 0, 12550, 0, 12550, '2017-02-10 21:28:05') ON DUPLICATE KEY UPDATE guardian_name = VALUES ('AJAY KUMAR') Error Info: Array ( [0] => 42000 [1] => 1064 [2] => You have an error – Ramesh Pareek Feb 10 '17 at 16:00
The unique key in my case is account_no, and this is my sql : INSERT INTO fee_acc_balance (guardian_name, account_no, paid_amount, due, days_overdue, total_fees, updated_on) VALUES ('$father_name', '$account->account_no', $payments, $sum, 0, $sum, '$now') ON DUPLICATE KEY UPDATE guardian_name = VALUES ('$father_name') – Ramesh Pareek Feb 10 '17 at 16:01
On completion of query how to figure out whether it was an update or insert? – joseph Mar 02 '17 at 14:53
4

@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.*" – eggyal Mar 02 '17 at 18:57
This is not working in sql console, give minus if is false! :) – Marcel Domuta Mar 23 '18 at 19:53
Important: this will increase the autoincrement on every run regardless of whether it UPDATEs or INSERTs, which could be an issue if run lots of these queries. – twhitney Mar 21 '22 at 22:55

score 2 · Answer 2 · edited Oct 16 '20 at 05:09

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

edited Oct 16 '20 at 05:09

Sabito stands with Ukraine

4,271
8
34
56

answered Oct 16 '20 at 04:34

Manish Kumar

41
1

1

This SQL script has tables/columns unrelated to OP's schema – Yehor Androsov Oct 16 '20 at 05:47
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. – twhitney Mar 21 '22 at 22:53

score 2 · Answer 3 · answered Mar 23 '22 at 16:12

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;

For the second solution, it has to be wrapped in a procedure, right? — Lusha Li, Jun 01 '22 at 07:53

SQL - IF EXISTS UPDATE ELSE INSERT INTO

3 Answers3

Linked

Related