MYSQL provides the
INSERT IGORE (Doc) statement which is quite useful when one need to insert rows into a table ignoring duplicates. Unfortunately, this syntax is not supported by all databases and thus an alternative solution is required.
Assume we have the following simple table with two columns:
CREATE TABLE `x` ( `id` INT(10) NOT NULL, `name` VARCHAR(64) DEFAULT NULL, PRIMARY KEY (`id`) );
We can achieve the
INSERT IGNORE using Plain Old SQL as shown next.
INSERT INTO `x` SELECT DISTINCT 1, 'ABC' FROM dual WHERE NOT EXISTS (SELECT * FROM `x` WHERE `id` = 1);
The above code makes use of the special table:
dual which is not available in all databases. If the special table:
dual is not supported by your database, simply replace this with a table that has only one record, called
one_record_table. In this case you need to create a table with the name:
one_record_table, that has one column (not that this is important for this example) and replace the special table:
`one_record_table`. It is imperative that this table has only one record as otherwise the above query will not work as expected.