• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2825
  • Last Modified:

Insert data into firebird using 'where not exists'

Hi, can anyone help me on this ?

I want to insert TWO fields using the following code: instead of

     with Algquery1 do      //zien of record aanwezig is in Teksttabel
         begin
          Close;
           SQL.Text:= 'INSERT INTO TEKST (TEKSTVERWERKER) '+
                      'SELECT CAST (:pTEKSTVERWERKER AS CHAR(50)) FROM RDB$DATABASE '+
                      'WHERE NOT EXISTS( SELECT * FROM TEKST '+
                      'WHERE UPPER(TEKSTVERWERKER) = UPPER(CAST (:pTEKSTVERWERKER AS CHAR(50)))) ';
           ParamByName('pTEKSTVERWERKER').AsString:= 'Acrobat reader';
          ExecSQL;
          Transaction.Commit;
         end;

     with Algquery1 do      //zien of record aanwezig is in Teksttabel
         begin
          Close;
           SQL.Text:= 'INSERT INTO TEKST (TEKSTVERWERKER, GEBRUIKER) '+
                      'SELECT CAST (:pTEKSTVERWERKER AS CHAR(50)) FROM RDB$DATABASE '+
                      'WHERE NOT EXISTS( SELECT * FROM TEKST '+
                      'WHERE UPPER(TEKSTVERWERKER) = UPPER(CAST (:pTEKSTVERWERKER AS CHAR(50)))) ';
           ParamByName('pTEKSTVERWERKER').AsString:= 'Acrobat reader';
          ExecSQL;
          Transaction.Commit;
         end;

Can anyone show me how to also fill in the 'GEBRUIKER' field ?

thx
Mario
0
marioway
Asked:
marioway
  • 5
  • 3
1 Solution
 
NickUpsonCommented:
is this what you want?
 SQL.Text:= 'INSERT INTO TEKST (TEKSTVERWERKER, GEBRUIKER) '+
                      'values (cast(:pTEKSTVERWERKER AS CHAR(50)), :GEB ')+
                      'WHERE NOT EXISTS( SELECT * FROM TEKST '+
                      'WHERE UPPER(TEKSTVERWERKER) = UPPER(CAST (:pTEKSTVERWERKER AS CHAR(50)))) ';
0
 
mariowayAuthor Commented:
so I don't have to look in the RDB$database to see if someone else already filled in the data ?

Mario
0
 
NickUpsonCommented:
my version still does the test for existance in TEKST to see if TEKSTVERWERKER already exists there,

but you don't need to involve rdb$database

if you want to test against both fields then extend the last part to included "and upper(GEBRUIKER = upper(:geb)

geb is a variable I added to hold the value you will need to get it from somewhere
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mariowayAuthor Commented:
ok, thx Nick

kind regards
Mario
0
 
mariowayAuthor Commented:
sorry Nick to bother you again, but I get an error ' WHERE'

           SQL.Text:= 'INSERT INTO TEKST (TEKSTVERWERKER, GEBRUIKER) '+
                      'VALUES(:pTEKSTVERWERKER, :pGEBRUIKER) '+
                      'WHERE NOT EXISTS(SELECT * FROM TEKST '+
                      'WHERE UPPER(TEKSTVERWERKER) = UPPER(:pTEKSTVERWERKER)) ';
           ParamByName('pTEKSTVERWERKER').AsString:= 'Acrobat reader';
           ParamByName('pGEBRUIKER').AsString:= GEBR;
0
 
NickUpsonCommented:
actually, rethinking this, your original plan was ok (so long as the volumes are small)

           SQL.Text:= 'INSERT INTO TEKST (TEKSTVERWERKER, GEBRUIKER) '+
                      'SELECT CAST (:pTEKSTVERWERKER AS CHAR(50)) FROM RDB$DATABASE '+
                      'WHERE NOT EXISTS( SELECT * FROM TEKST '+
                      'WHERE UPPER(TEKSTVERWERKER) = UPPER(CAST (:pTEKSTVERWERKER AS CHAR(50)))) ';

becomes

           SQL.Text:= 'INSERT INTO TEKST (TEKSTVERWERKER, GEBRUIKER) '+
                      'SELECT CAST (:pTEKSTVERWERKER AS CHAR(50)), :gebr FROM RDB$DATABASE '+
                      'WHERE NOT EXISTS( SELECT * FROM TEKST '+
                      'WHERE UPPER(TEKSTVERWERKER) = UPPER(CAST (:pTEKSTVERWERKER AS CHAR(50)))) ';

if the tables is significant size you will need a unique index on the fields and catch the exception or use UPDATE or INSERT ... if you have firebird 2.0 or higher
1
 
mariowayAuthor Commented:
ok nick I'm going to take a look at this tomorrow

thx
Mario
0
 
mariowayAuthor Commented:
OK Nick, everythring is working as expected; the tables are nog big

thx a lot

regards
Mario
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now