Re: [Q]: INSERT with NOT IN Subquery??

ericesau@dimensional.com wrote: > > I am trying to speed up a MS-Access application which is using > Informix-CLI to access the mainframe database. I am trying to figure out > how I can insert a row into a table ONLY if it doesn't already exist. I > am trying to use SQL pass-through queries exclusively. The following are > two statements I have tried that do not work: > > INSERT INTO employees (EmployeeID) VALUES (100) WHERE NOT EXISTS > (SELECT * from employees WHERE EmployeeID = 100)> > INSERT INTO employees (EmployeeID) VALUES (100) WHERE 100 NOT IN > (SELECT EmployeeID from employees)> > Both statements use a where clause which seems to cause the query not to > work. Any help would be appreciated. I'm a Access programmer not a SQL > programmer. You cannot do what you want to do directly. INSERT statements cannot contain a WHERE clause. If the table has a unique index or UNIQUE CONSTRAINT then just execute the INSERT and it will fail with an error -239 (duplicate value in a unique index) or -268 (unique constraint violation). However if there is not index or constraint to enforce uniqueness, and even if there is since this method will tend to be faster (a failed insert has to be rolled back), you can use a SELECT statement to determine if the row exists before trying to insert: SELECT count(*) FROM employees WHERE employeeid = 100; This will return a zero count if the row does not exist and >1 if it does. Then you can decide whether to INSERT the new row. If what you want to update the existing row if it does exist, it is usually faster to do the UPDATE and if it fails because the row does not exist then execute the INSERT instead. This will usually be the fastest method if >25-30% of the rows being INSERTED/UPDATED already exist. If only a few of the rows already exist then you will have to use one of the other methods to determine whether to INSERT or UPDATE. Remember that even with a unique constraint/index a failed insert, due to non-uniqueness, will be relatively slow since the row is physically added to the database then indexes are updated, if there are several indexes the unique index may not be the first one updated, this is when the uniqueness is checked, and all of the work must be rolled back if the uniqueness requirement is violated. This rollback takes time. Art S. Kagel