Conditional insert question

Forum Moderators: open

Message Too Old, No Replies

Conditional insert question

endtheme

3:06 am on Jul 15, 2008 (gmt 0)

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

3:40 am on Jul 15, 2008 (gmt 0)

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

endtheme

4:06 am on Jul 15, 2008 (gmt 0)

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

5:44 am on Jul 15, 2008 (gmt 0)

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

6:57 am on Jul 15, 2008 (gmt 0)

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

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

bmcgee

2:39 am on Jul 17, 2008 (gmt 0)

How about:

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