MySQL WHERE NOT EXISTS error

Question

Let's say I have two columns member_id, email in one table users. I'm trying to add a new row if no similar data is found with below statement:

INSERT INTO users(member_id, email) 
VALUES (1,'k@live.com') 
WHERE NOT EXISTS (SELECT * FROM users WHERE member_id=1 AND email='k@live.com');

However, it's not working. #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 EXISTS

Please shed some light. Thanks.

thank you, that has just reminded me in adding a unique constraint! — user3153745, Feb 08 '14 at 07:31

score 1 · Accepted Answer · edited May 23 '17 at 12:02

1

Assuming you have a unique constraint on member_id, email or a combination of both, I believe you will be better served with an INSERT IGNORE, if the record doesn't exist, it will be inserted.

INSERT IGNORE INTO users(member_id, email)
values (1, 'k@live.com');

If there is no unique constraint, use this technique here

INSERT INTO users(member_id, email) 
SELECT 1,'k@live.com'
  FROM dual 
  WHERE NOT EXISTS 
    (SELECT * FROM users WHERE member_id=1 AND email='k@live.com');

Dual is used in the dummy select rather than users in order to limit the rows inserted to 1.

edited May 23 '17 at 12:02

Community

1
1

answered Feb 08 '14 at 07:06

StuartLC

96,413
17
181
256

2

INSERT IGNORE is applicable if table has unique constraints – shola Feb 08 '14 at 07:20
@shola thanks - I've asserted the assumption and included an alternative if this isn't the case. – StuartLC Feb 08 '14 at 08:02

spencer7593 · Answer 2 · 2014-02-08T07:39:17.367

There cannot be a WHERE clause in an INSERT ... VALUES ... statement.

The normal pattern for avoiding duplicates is to add UNIQUE constraint(s). If you want to avoid adding any duplicate "member_id" values, and you also want to avoid adding any duplicate "email" values, then

CREATE UNIQUE INDEX mytab_UX1 ON mytab (member_id);
CREATE UNIQUE INDEX mytab_UX2 ON mytab (email);

Whenever an INSERT or UPDATE attempts to create a duplicate value, a duplicate key exception (error) will be thrown. MySQL provides the IGNORE keyword which will suppress the error, and allow the statement to complete successfully, but without introducing any duplicates.

Given an empty table, the first statement would insert a row, the second and third statements would not.

INSERT IGNORE INTO mytab (member_id, email) VALUES (1,'k@live.com');
INSERT IGNORE INTO mytab (member_id, email) VALUES (2,'k@live.com');
INSERT IGNORE INTO mytab (member_id, email) VALUES (1,'aaa@bbb.com');

If you want to restrict just the combination of the two columns to being unique, that is you would allow the 2nd and 3rd statements to insert a row, then you'd add a UNIQUE constraint on the combination of the two columns, rather than two separate unique indexes as above.

CREATE UNIQUE INDEX mytab_UX1 on mytab (member_id, email);

Aside from that convention, say you don't have a unique constraint, but you only want to modify the behavior of the single insert statement, then you can use a SELECT statement to return the values you want to insert, and then you can add a WHERE clause to the SELECT.

To avoid adding any duplicate member_id or duplicate email, then something like this would accomplish that:

 INSERT INTO mytab (member_id, email)
 SELECT s.member_id, s.email
   FROM (SELECT 1 AS member_id, 'k@live.com' AS email) s
  WHERE NOT EXISTS (SELECT 1 FROM mytab d WHERE d.member_id = s.member_id)
    AND NOT EXISTS (SELECT 1 FROM mytab e WHERE e.email = s.email)

For best performance with a large table, you're going to want at least two indexes, one with a leading column of member_id, and one with a leading column of email. The NOT EXISTS subqueries can make use of an index to quickly locate a "matching" row, rather than scanning every row in the table.)

Again, if it's just the combination of the two columns you want to be unique, you'd use a single NOT EXISTS subquery, as in your original query.

Alternatively, you could use an anti-join pattern as an equivalent to the NOT EXISTS subquery.

INSERT INTO mytab (member_id, email)
SELECT s.member_id, s.email
  FROM (SELECT 2 AS member_id, 'k@live.com' AS email) s
  LEFT
  JOIN mytab d
    ON d.member_id = s.member_id
  LEFT
  JOIN mytab e
    ON e.email = s.email
 WHERE d.member_id IS NULL
   AND e.email IS NULL

MySQL WHERE NOT EXISTS error

2 Answers2