CODEWITHSUNDEEP
mysqlsql
Ted
Ted

Reputation: 4166

MySQL: insert where not exists

I'm trying to insert new user if not exist in users table, I tried this query but receive error:

INSERT INTO USER (name,email)
VALUES ('John','[email protected]')
WHERE NOT EXISTS
    (SELECT id FROM USER WHERE email = '[email protected]')

how to insert user if not exists ?

error:

#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 'where not exists (select id from user where email = '[email protected]')' at line 5

thanks,

Upvotes: 7

Views: 17546

Answers (3)

JBB
JBB

Reputation: 333

Use the INSERT IGNORE query.

If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.

mysql> INSERT IGNORE INTO USER (name, email)
   -> VALUES( 'Jay', '[email protected]');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO USER (name, email)
   -> VALUES( 'Jay', '[email protected]');
Query OK, 0 rows affected (0.00 sec)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Use insert . . . select:

INSERT INTO USER (name, email)
    SELECT 'John', '[email protected]'
    WHERE NOT EXISTS
        (SELECT id FROM USER WHERE email = '[email protected]');

I would write this as:

INSERT INTO USER (name, email)
    SELECT name, email
    FROM (SELECT 'John' as name, '[email protected]' as email) t
    WHERE NOT EXISTS (SELECT 1 FROM USER u WHERE u.email = t.email);

But a better approach is probably to just put in a unique index so the database protects the data:

create unique index idx_users_email on user(email);

Upvotes: 16

Bacon Bits
Bacon Bits

Reputation: 32145

Use INSERT ... SELECT instead of INSERT.

INSERT INTO USER (name,email)
SELECT 'John','[email protected]'
WHERE NOT EXISTS
    (SELECT id FROM USER WHERE email = '[email protected]')

You also might consider using the ON DUPLICATE KEY UPDATE syntax extension of MySQL.

Upvotes: 10

Related Questions