INSERT INTO only if not exists MYSQL

Question

I'm currently insert data by the way of using SELECT and not VALUES as it is by default. The problem is that I can't find a way to insert data only if it's not in the database.

This is my current query:

INSERT INTO collectives_users(id_user,id_artistname,id_collective,users_type,status)
SELECT
    (SELECT id_user FROM artistnames WHERE artistname = 'yoannis'),
    (SELECT id FROM artistnames WHERE artistname = 'yoannis'),
    ('1'),(2),(0)
UNION ALL
SELECT
    (SELECT id_user FROM artistnames WHERE artistname = 'paul'),
    (SELECT id FROM artistnames WHERE artistname = 'paul'),
    ('1'),(4),(0)

I tried with

INSERT IGNORE INTO

Doesn't work and not really appropriate (not returning error)

I also tried with

AND NOT EXIST

Doesn't work.

If you have an idea don't hesitate.

try to parenthese your union like select * from ( select ... union all select ... ) _t where ... not in ( select ... from collective_users ) — Mirco Ellmann, May 04 '13 at 15:51
BTW, I don't want to update, I just want to ignore the insert like if it never exist — Simon, May 04 '13 at 16:00
there are 2 things: the existing rows previously to your insert and the duplicate rows while the insert is being processed. Which case is worrying you? Also, define "only if it's not in the database" please — Sebas, May 04 '13 at 18:41
the suggested duplicate is actually not solving this specific problem — Sebas, May 04 '13 at 18:42
@Simon Let me see if I understand correctly...you *want* to insert non-duplicates only, but `INSERT IGNORE` doesn't work *because* it doesn't return an error? So if that's the case...why not just use `INSERT` without `IGNORE`? Your question makes no sense. — Jesse, May 04 '13 at 19:03
@Jesse Error are executed as warning if you use IGNORE http://dev.mysql.com/doc/refman/5.5/en/insert.html Like if you duplicate a key, you're not going to have the error duplicate-key error even if it's aborted. My question was "If you are already in my DB, do nothing, if you are not, insert in DB" no update or replace or whatever :) — Simon, May 05 '13 at 21:38
I found the solution to my problem so I don't need more "ignore" and I can have access to errors (but I can't publish here because it's "duplicate" question or something else) — Simon, May 05 '13 at 21:57

GregD · Answer 1 · 2013-05-04T16:11:39.367

~~Have you tried the REPLACE syntax? It works the same as INSERT, only use REPLACE instead of INSERT.~~

How about setting a dummy column in the collectives_users table, so we could for example count times a duplicate was found. This way, we could use the INSERT ... ON DUPLICATE KEY UPDATE syntax:

INSERT INTO collectives_users(id_user,id_artistname,id_collective,users_type,status)
SELECT
    (SELECT id_user FROM artistnames WHERE artistname = 'yoannis'),
    (SELECT id FROM artistnames WHERE artistname = 'yoannis'),
    ('1'),(2),(0)
UNION ALL
SELECT
    (SELECT id_user FROM artistnames WHERE artistname = 'paul'),
    (SELECT id FROM artistnames WHERE artistname = 'paul'),
    ('1'),(4),(0)
ON DUPLICATE KEY UPDATE dummy_column = dummy_column + 1;

Another way to do it is to create a stored procedure. This is more tricky.

Sample code:

DELIMITER \\
DROP PROCEDURE IF EXISTS sp_test\\
CREATE PROCEDURE sp_test(in_id_user, in_id_artistname, in_id_collective, in_users_type, in_status)
BEGIN

CASE
    WHEN NOT EXISTS (SELECT id_user FROM artistnames WHERE artistname = 'yoannis')  OR ...
    THEN INSERT ...
END CASE;

END\\
DELIMITER ;

More info at MySQL Reference Manual for INSERT ... ON DUPLICATE KEY UPDATE and CASE syntax1.

`replace` will delete and re-add a row. It's often better to use `insert ... on duplicate key update` — Andomar, May 04 '13 at 15:51
You are updating value here, I don't want to do it. I juste want to move away from the insert and test the next one. — Simon, May 04 '13 at 16:01
just remove `+1` example `ON DUPLICATE KEY UPDATE dummy_column = dummy_column;` — John Woo, May 04 '13 at 16:06
@JW웃 +1 doesn't remove and do an update too so not what i was looking for (and I tried, it doesn't works :/) — Simon, May 05 '13 at 21:34

score 0 · Answer 2 · edited May 23 '17 at 12:28

As you have one UNIQUE you can use ON DUPLICATE KEY UPDATE running no risk, and duplicate values will be updates so that there is no duplicate, as with the idea of a dummy column:

INSERT INTO collectives_users(id_user,id_artistname,id_collective,users_type,status)
SELECT
    (SELECT id_user FROM artistnames WHERE artistname = 'yoannis'),
    (SELECT id FROM artistnames WHERE artistname = 'yoannis'),
    ('1'),(2),(0)
UNION ALL
SELECT
    (SELECT id_user FROM artistnames WHERE artistname = 'paul'),
    (SELECT id FROM artistnames WHERE artistname = 'paul'),
    ('1'),(4),(0)
ON DUPLICATE KEY UPDATE dummy_column = dummy_column + 1;

I advice that you use WHERE NOT EXISTS instead

INSERT INTO collectives_users(id_user,id_artistname,id_collective,users_type,status)
SELECT
    (SELECT id_user FROM artistnames WHERE artistname = 'yoannis'),
    (SELECT id FROM artistnames WHERE artistname = 'yoannis'),
    ('1'),(2),(0)
UNION ALL
SELECT
    (SELECT id_user FROM artistnames WHERE artistname = 'paul'),
    (SELECT id FROM artistnames WHERE artistname = 'paul'),
    ('1'),(4),(0)
WHERE NOT EXISTS(SELECT Id 
       FROM collectives_users t2
       WHERE t2.Id = artistnames.Id

I did not try the code, but it is a flavor of where you can look at. Also, more on 'WHERE NOT EXISTS` in this thread.

INSERT INTO only if not exists MYSQL

2 Answers2