Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!sdd.hp.com!hplabs!nsc!pyramid!infmx!randall
From: randall@informix.com (Randall Rhea)
Newsgroups: comp.databases
Subject: Re: Effecient merging of tables in SQL ? (informix V2.10)
Keywords: sql, informix, merge
Message-ID: <1990Nov8.021252.27609@informix.com>
Date: 8 Nov 90 02:12:52 GMT
References: <1990Nov7.115344.27844@diku.dk>
Sender: news@informix.com (Usenet News)
Distribution: comp
Organization: Informix Software, Inc.
Lines: 39

In article <1990Nov7.115344.27844@diku.dk> nomann@rimfaxe.diku.dk (Ole Nomann Thomsen) writes:
>Hello all. I am doing some programming in informix-sql V2.10, and have run 
>into a problem that I can solve (:-)) , but only inefficiently (:-().
>
>Now this statement (repeated from jelly-ware memory) does it:
>
>update main
>  set l_num = (
>    select lnum from T1                             {1}
>    where T1.no = main.s_no                         {1}
>  )
>where exists                                        {2}
>    select no from T1                               {2}
>    where T1.no = main.s_no;                        {2}
>
>But it's cruciatingly slow:

Temporary tables can really speed things up in Informix, especially for
ACE reports.  This will work:

SELECT
   no, lnum
   FROM T1, main
   WHERE T1.no = main.s_no
   INTO TEMP tempmain;
UPDATE main SET
   l_num = (SELECT lnum FROM tempmain WHERE tempmain.no = main.s_no)
   WHERE s_no IN (SELECT no FROM tempmain);

Hopefully, that will be a lot faster.   There may be a better way to
do it. (without a TEMP table)  However, Informix-SQL is a bit limited
when it comes to performing an UPDATE while looking up information
in more than one table.

-- 

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Randall Rhea                                          Informix Software, Inc. 
Senior Programmer/Analyst, MIS                    uunet!pyramid!infmx!randall