ignore insert of rows that violate duplicate key index

Question

I perform an insert as follows:

INSERT INTO foo (a,b,c)
   SELECT x,y,z
   FROM fubar
   WHERE ...

However, if some of the rows that are being inserted violate the duplicate key index on foo, I want the database to ignore those rows, and not insert them and continue inserting the other rows.

The DB in question is Informix 11.5. Currently all that happens is that the DB is throwing an exception. If I try to handle the exception with:

ON EXCEPTION IN (-239)
END EXCEPTION WITH RESUME;

... it does not help because after the exception is caught, the entire insert is skipped.

I don't think informix supports INSERT IGNORE, or INSERT ... ON DUPLICATE KEY..., but feel free to correct me if I am wrong.

Answer 1

Use IF statement and EXISTS function to check for existed records. Or you can probably include that EXISTS function in the WHERE clause like below

INSERT INTO foo (a,b,c) 
SELECT x,y,z 
FROM fubar 
WHERE (NOT EXISTS(SELECT a FROM foo WHERE ...))