Re: INSERT with NOT IN Subquery??

Vasili M. Kolovos wrote: > > Seems to me you would be better off checking for the existance of said > record prior to trying to insert. In fact, as far as I know (which isn't > necessarily very far) there is no "where" clause in an "insert" statement. > > }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. > } > }Thanks > } > }-------------------==== Posted via Deja News ====----------------------- > } http://www.dejanews.com/ Search, Read, Post to Usenet > } Right, INSERT does not have a WHERE clause. There is a trick to use, but I don't know whether it is possible in MS-Access: Create unique constraint or unique index on 'employees(EmployeeID)'. Then, yust for this statement, set erro handling of 'duplicate value in unique column' error to ignore, so that record gets inserted if ID does not exist, and silently ignored otherwise. Of course, this works only if this insert is the only thing you wish to do (so that nothing has to be rolled back in the case of this error etc). Besides, this is not exaclty a stellar example of good programming practice :-) HTH Dragi "Bonzi" Raos +--------------------------------------------------------+ | 4-MATE Information Engineering Inc. | +--------------------------+-----------------------------+ | 4-MATE | Tel: +385 (1) 242-116 | | Kosa bb | +385 (1) 242-126 | | 10000 Zagreb | Fax: +385 (1) 242-121 | | Hrvatska (Croatia) | e-mail: bonzi@4mate.hr | | | URL: http://www.4mate.hr | +--------------------------+-----------------------------+