INSERT INTO only if not exists MYSQL [duplicate]

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.

Answer 1

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.

Answer 2

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.