MySQL - insert if doesn't exist yet

Question

I want to execute this MySQL query:

INSERT INTO `cron-stats` (`user`) VALUES (".(int)$d['by-user'].")

Whenever such user doesn't exist yet, as in:

SELECT 1
FROM `cron-stats`
WHERE `user` = ".(int)$d['by-user']."

How can I execute this in one query?

Have you already studies the [`INSERT` syntax reference page](http://dev.mysql.com/doc/refman/5.6/en/insert.html)? — Gumbo, Jan 26 '13 at 07:49
possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — Jocelyn, Jan 26 '13 at 23:52

score 22 · Accepted Answer · answered Jan 26 '13 at 07:47

22

you can use ON DUPLICATE KEY UPDATE

INSERT INTO `cron-stats` (`user`) VALUES ('yourValue')
ON DUPLICATE KEY UPDATE user = user;

ON DUPLICATE KEY UPDATE

but in order to perform the INSERT statement well, you need to set a UNIQUE index on column user.

if the column has no index yet, execute the statement below,

 ALTER TABLE `cron-stats` ADD CONSTRAINT tb_un UNIQUE (`user`)

answered Jan 26 '13 at 07:47

John Woo

258,903
69
498
492

Not sure if this helps. I'm using this in a CRON, where I'm running ``UPDATE`` for existing entries. Before the ``UPDATE`` runs, I want to prepare not existing entries by INSERTing null values. Is this the proper solution? – Frantisek Jan 26 '13 at 07:50
what's the reason why you need to insert null values first? – John Woo Jan 26 '13 at 07:51
Oh well, it doesn't work. Here's the full query with the error: http://pastebin.com/PyHMY9RG – Frantisek Jan 26 '13 at 07:55
you have key word `SET` in the query which is not suppose to be there. remove it and execute it again. – John Woo Jan 26 '13 at 07:57
it's not the same. Oh, i see a `WHERE` clause at the end of the query. remove it also. – John Woo Jan 26 '13 at 07:59
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/23405/discussion-between-jw-and-rimmer) – John Woo Jan 26 '13 at 08:00
Interestingly, when you use the 'duplicate_key_update' statement and it prevents an insert, it still advances the AUTO_INCREMENT for that table. – Rich S Mar 08 '18 at 11:49

score 6 · Answer 2 · answered Jan 26 '13 at 07:52

6

A little bit hacky, but if you use a SELECT derived table instead of VALUES you can do:

INSERT INTO `cron-stats`(`user`)
SELECT u
FROM (SELECT @dByUser AS u) x
WHERE NOT EXISTS(SELECT 1 FROM `cron-stats` WHERE `user` = @dByUser)

SQL Fiddle demo

answered Jan 26 '13 at 07:52

lc.

113,939
20
158
187

2

Is this guaranteed to be atomic? That is, if two inserts come in at the same time, could they both pass the WHERE NOT EXISTS part and both insert? – Cruncher Sep 27 '18 at 13:35

score -3 · Answer 3 · answered Jan 26 '13 at 07:57

-3

You can try this using if else condition

$chk = mysql_query("select 'the username' FROM `cron-stats`");
$rs = mysql_fetch_array($chk);
if($rs == "")
{
    $ins = mysql_query("INSERT INTO `cron-stats` (`user`) VALUES ('.(int)$d['by-user'].')");
} 
else{
    echo "Duplicate entry";
}

answered Jan 26 '13 at 07:57

Bkay

177
2
4
16

2

This is a terrible solution since you're losing out hugely on performance benefits and ACID. You'd really need to wrap this in a transaction to make it work right, and why would you do that anyway when there are clear commands that are so much better? – Brett Sep 09 '14 at 17:01

MySQL - insert if doesn't exist yet

3 Answers3

Linked