Posts Tagged ‘insert’

Posted by 6bytes at 15 April 2011

Category: MySQL

Tags: , ,

Sometimes I need to insert some values into the database but only when a certain condition is met. The best example is a newsletter sign up. If someone has already signed up to your newsletter don’t add his email address again. Of course we can run one query to check if this email address already exists and if not run another query to save it in our database.
If for any reason you need to do the whole operation in one query things get a little more complicated.

Two queries

Lets say we want to insert some data only when field `email` does not equal test@example.com
First, select data:

SELECT `id` FROM `table` WHERE `email` = 'test@example.com';

if this email address already exists in our database we can let our users know about it. If no records were returned we can continue to insert new data:

INSERT INTO `table` (`email`) VALUES ('test@example.com');

One query

In a way we combine those two queries above into one:

INSERT INTO `table` (`email`)
    SELECT 'test@example.com' FROM DUAL WHERE NOT EXISTS (
        SELECT `id` FROM `table` WHERE `email` = 'test@example.com' LIMIT 1
    );

If the subquery doesn’t return any rows the insert will be carried out. In case of subquery finding this given email address condition is not met and data doesn’t get inserted.