what is the correct syntax for WHERE NOT EXISTS

Question

I am trying to prevent duplicate entries in a MySQL table. I've tried INSERT IGNORE but it doesn't seem to work. I believe that this is because I have an auto increment column and thusly no two rows will ever be duplicates.

So, I am trying to use WHERE NOT EXISTS like this

$sql = "INSERT INTO testresults (ttime, resno, course, vid, playdate) VALUES (:ttime, :resno, :course, :vid, :playdate)
 WHERE NOT EXISTS (SELECT * FROM tempresults WHERE vid = :vid AND playdate = :playdate) LIMIT 1)";

but this returns

SQLSTATE[42000]: Syntax error or access violation: 1064

There are numerous examples on Stack Overflow but they all seem to want the inserted values to come come from a SELECT statement; whereas I am inserting from programmatically generated values.

I think I just need a little tweak to the SQL stateent.

Thanks!

Do you perhaps mean to have your `LIMIT 1 ` _inside_ the parenthesis associated with the `SELECT`? And the remove the final closing (and unmatched) parenthesis. — Patrick Q, Jan 10 '20 at 16:46
Confused about your goal. You're combining the syntax for inserting one row with specified values with the syntax for copying multiple rows from another table. Which do you want to do here? — Greg Schmidt, Jan 10 '20 at 16:46
see this might help https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql — Dlk, Jan 10 '20 at 16:49
You don't/can't use `WHERE` with an `INSERT...VALUES(value_list)` query; but you can use it with an `INSERT...SELECT value_list WHERE ...` query. — Uueerdo, Jan 10 '20 at 17:28
To clarify. I want to insert a row into a table but I want to make certain it is not a duplicate row. The schema for the table includes the field uniq which is an auto increment field — Dave Davis, Jan 10 '20 at 17:59
`INSERT .. VALUES` allows neither `WHERE` nor `LIMIT`. Only `INSERT .. SELECT` allows these clauses. Read [MySQL 8.0 Reference Manual / ... / INSERT Statement](https://dev.mysql.com/doc/refman/8.0/en/insert.html). — Akina, Jan 10 '20 at 18:05

score 1 · Answer 1 · answered Jan 10 '20 at 18:52

I think INSERT IGNORE can work.

You said

I believe that this is because I have an auto increment column and thusly no two rows will ever be duplicates.

That is true, but based on what it looks like you're attempting with the SQL in your question, a duplicate row for your purposes can be identified based on the vid and playdate columns.

You can add a unique constraint on those two columns.

ALTER TABLE testresults ADD UNIQUE unique_vid_playdate (vid, playdate)

Then INSERT IGNORE will do what you want it to.

score 0 · Answer 2 · answered Jan 10 '20 at 19:01

I case you don't have a unique constraint then replace VALUES with this SELECT:

INSERT INTO testresults (ttime, resno, course, vid, playdate) 
SELECT :ttime, :resno, :course, :vid, :playdate
WHERE NOT EXISTS (SELECT * FROM tempresults WHERE vid = :vid AND playdate = :playdate)

There is no need for LIMIT since the statement will return max 1 row.

what is the correct syntax for WHERE NOT EXISTS

2 Answers2