Re: [firebird-support] Re: How to insert only if a matching row does not exist?

[Ismael L. Donis Garcia]

I do not believe that that way be able to
You should make a stored procedure with:

SELECT 1 FROM T1 WHERE name = :imput_parameter
if (row_count = 0) then
INSERT INTO T1 (type, name, sysid, flag) values (1, :imput_parameter, 1, 0);


Best Regards
=========
|| ISMAEL ||
=========
  ----- Original Message ----- 
  From: gastrocus 
  To: firebird-support@yahoogroups.com 
  Sent: Thursday, October 20, 2011 12:28 PM
  Subject: [firebird-support] Re: How to insert only if a matching row does not 
exist?


    


  --- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
  >
  > >What is the analogous way to achieve this in Firebird (2.5) ?
  > 
  > INSERT INTO emp (fruits) values ('mango')
  > where not exists (select 1 from emp where fruits = 'mango')

  Thanks for the quick reply.

  Strange... when I try to do this (via flamerobin or ibexpert) it does not 
recognize the "Where" token (Dynamic SQL error -104, Token unknown, where ). 
Must be something simple :(

  Here is what I am trying:

  I have a table like the following:
  CREATE TABLE T1 (
  ID INTEGER NOT NULL,
  TYPE INTEGER,
  NAME VARCHAR(128),
  SYSID INTEGER,
  FLAG INTEGER
  );

  ID has a generator so I don't want to insert a value for it.

  Here is the query I tried

  INSERT INTO T1 (type, name, sysid, flag) values (1, 'ZZZZZ', 1, 0)
  WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE name = 'ZZZZZ')

  It 
  > 
  > Look up INSERT OR UPDATE and also MERGE in the Language Reference Update. 
One or both might do what you want, more efficiently than NOT EXISTS. Not 
enough info here to guess what you're really going to do with it.
  > 

  My application is really to write a script that will be run once on a 
smallish dataset (on the order of 1000 rows) so performance isn't really a big 
deal.

  Thanks for the pointer. I did look at INSERT OR UPDATE prior to posting but I 
do not want to UPDATE if there is a match so I didn't think this applied.



  

[Non-text portions of this message have been removed]