How To INSERT IGNORE into a Table using Plain Old SQL

5 years ago by in How To, SQL, Utilities Tagged: , ,

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: dual with `one_record_table`. It is imperative that this table has only one record as otherwise the above query will not work as expected.

Albert Attard

Albert Attard is a Java passionate and technical lead at a research group. You can find him on . Over the past years Albert worked on various Java projects including traditional server/client applications, modular applications, large data handling applications and concurrent data manipulation applications to name a few. He has a BSc degree from the University of London (Homepage) and an MSc Information Security with the same university. His MSc thesis (Book) received the 2012 SearchSecurity.co.UK award (Website).

Leave a Comment


Time limit is exhausted. Please reload the CAPTCHA.