NetNews Usenet Archive 1992 #20/NN_1992_20.iso/spool/comp/database/informix/1925

Internet Message Format | 1992-09-14 | 3.0 KB

Path: sparky!uunet!wupost!emory!obelix.informix.com
From: [e-mail] (Jonathan Leffler)
Newsgroups: comp.databases.informix
Subject: Re: HELP! Trying to UPDATE temp table HELP!
Message-ID: <[e-mail]>
Date: 14 Sep 92 12:23:07 GMT
Sender: [e-mail]
Reply-To: [e-mail] (Jonathan Leffler)
Lines: 59
X-Informix-List-ID: <list.1444>

>From: Dave Snyder <uunet!widener!das13!dave.snyder>
>Subject: Re: HELP! Trying to UPDATE temp table HELP!
>Date: Mon, 7 Sep 92 14:27:56 EDT
>X-Informix-List-Id: <list.1432>
>
>Quoting Mark L Trotter...
>}
>}    I am trying to create a temporary table, insert some elements from
>} another table, then go thru the temporary table using a cursor and
>} do some updating of other elements.  All works except:
>}    1. I can't DEFINE a memory record LIKE the temporary table because
>}       it doesn't exist at compile time so I assume I must define the
>}       entire memory record via listing all of the elements, but if I do
>}       that then:
>}    2. I can't UPDATE the temporary table using
>}        UPDATE temptablename SET temptablename.* = memrecord.*
>}       because I get a compile error saying that memrecord was not
>}       defined using the RECORD LIKE phrase.
>} Is this a know bug and is there any workarounds?

No, it is not a bug but it is a known pair of restrictions.

>Try this:
>DEFINE the record the "long hand" way and then use an UPDATE statement like
>this one...
>
>UPDATE temptablename SET (temptablename.col1, temptablename.col2, etc...)
>               = (memrecord.col1, memrecord.col2, etc...)

As an alternative, define a view which (a) contains no rows, and (b)
contains the required column types, and compile against that view.

This may not work with temporary tables, but I do use the technique for
input where I need data from a number of different tables.  The sort
of view I have in mind is:

CREATE VIEW Compiler_view_001 (ColumnA, ColumnB, ...) AS
    SELECT A.Column1, B.Column2, ...
        FROM TableA A, TableB B, ...
        WHERE 1 = 0;

The other alternative is to create a permanent table with the required
columns and use that instead of the temporary table.  This is often not
satisfactory in a multi-user environment, but sometimes it can work.
I have a batch program that grabs the database in exclusive mode and
uses a permanent table as an intermediate results file; when the program
completes, it drops the table and rebuilds it.  The program is run once
a week in the wee small hours (about 04:00) and this works quite nicely
in this case, thank you.  It may not be relevant for your problem -- sorry.

Finally, you could try preparing the UPDATE statement and execute it
with a USING clause listing all the elements in the record.  You will need
to worry about supplying the values for the WHERE clause as well as the SET
clause.  If you prepare it just once, your performance will also improve
(provided you execute it more than once), though the difference may not
be easily measurable.

Yours,
Jonathan Leffler ([e-mail]) #include <disclaimer.h>