Conditional insert question

[endtheme]

I am trying to do a conditional insert but this query seems to fail at the WHERE NOT EXISTS clause.

I want to insert the value 'John Doe' into the field 'b' IF the value 'John Doe' does not already exist in any rows of the 'b' field for table1.

INSERT INTO table1 (b)
VALUES ('John Doe')
WHERE NOT EXISTS (SELECT * FROM table1 WHERE b = 'John Doe')

[phranque]

you could try the "EXPLAIN SELECT..." syntax and see if that gives you a hint.

[endtheme]

Hmm. Thanks but it still gives me the same syntax error message.

#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 * FROM ....

[phranque]

actually, INSERT syntax does not allow a WHERE clause.
you could probably accomplish the same with:
INSERT INTO table1 SET b='John Doe'

who cares if it replaces an existing value with the same?

[dreamcatcher]

If you want to simply ignore duplicate entries, use IGNORE. This returns false with no errors:

INSERT IGNORE INTO table1 (b) VALUES ('John Doe');

dc

[bmcgee]

How about:

INSERT INTO table1 (b)
SELECT 'John Doe'
WHERE NOT EXISTS (SELECT * FROM table1 WHERE b = 'John Doe')