Site Trick

Sometimes you need to check to see if the record is a duplicate before inserting it into the database. The simplest way is to do a SELECT query first and if that query returns a row, skip the INSERT query.

INSERT INTO table (field) VALUES ('value') WHERE NOT EXISTS (SELECT id FROM table WHERE field='value')

This query makes total sense except that it returns an error.

The proper way of doing this using a single query is by utilizing the DUAL dummy table, as so:

INSERT INTO posts (title,body) SELECT 'The Title of my Post', 'This is the body of my post.' FROM dual WHERE not exists (SELECT id FROM posts WHERE body LIKE CONVERT( _utf8 'This is the body of my post.%' USING latin1 ));

You can also use the REPLACE INTO syntax:
INSERT INTO posts (title,body) SELECT 'The Title of my Post', 'This is the body of my post.' FROM dual WHERE not exists (SELECT id FROM posts WHERE body LIKE CONVERT( _utf8 'This is the body of my post.%' USING latin1 ));