Re: Table lookup function in isql?

In article <62980009@col.hp.com> judym@col.hp.com (Judy Miller) writes: >Question: Is there some kind of table lookup feature in isql > (Informix SQL Query Language)? > >Problem: I have a list of keys to 4000 records which I need to delete from > a table of 12000. > >I'm trying to avoid writing a program for this common function. >(I do not have 4GL on this machine, but I do have ESQL/C.) > >Another option is to unload the table and eliminated the records outside of >Informix, delete all, and then reload the remaining two-thirds. > >Judy (sorry if this gets posted twice) What I tend to do is write a script that will generate a script that will do the deletes. Not too elegant, but it works well. e.g. Suppose table "to_be_nuked" contains all the keys of the rows to be deleted in the column "key". Suppose "big_table" is the table that we are to delete from, with the keysin column "ID" Then the following script: select "delete from big_table where ID = " a, key b, ";" c from to_be_nuked will output a file that looks like this: a delete from big_table where ID = b 23 c ; a delete from big_table where ID = b 57 c ; a delete from big_table where ID = b 103 c ; a delete from big_table where ID = b 106 c ; (etc) Now pipe this through "cut -c2-80", and the resulting file is a script that will do the job. You can use variations on this method to do all sorts of mass-updates/ deletes to large tables. Paul