Topic: mysql conditional insert

[ober]

In MySQL, is there a way to run a single query that will insert only if a key doesn't already exist?

The query keeps bitching at me for trying to put a WHERE clause on an INSERT but there has to be a better way than going to the database twice (select and check row count and then insert if 0), right?

[Perspective]

What language are you calling it from? You should be able to catch the duplicate key error.

[Mike]

Ok, first, if the key exists do you want to update the non-key columns or fail?

If you want to update you can do a REPLACE INTO but this isn't always ideal as it'll delete the previous row and insert a new one.  You can also do a INSERT INTO ... ON DUPLICATE KEY UPDATE

If you want to just fail then use INSERT IGNORE