Path: utzoo!utgpu!news-server.csri.toronto.edu!rutgers!gatech!ncar!elroy.jpl.nasa.gov!turnkey!orchard.la.locus.com!prodnet.la.locus.com!jfr
From: jfr@locus.com (Jon Rosen)
Newsgroups: comp.databases
Subject: Re: Effecient merging of tables in SQL ? (informix V2.10)
Keywords: sql, informix, merge
Message-ID: <19367@oolong.la.locus.com>
Date: 10 Nov 90 03:04:40 GMT
References: <1990Nov7.115344.27844@diku.dk> <1990Nov8.021252.27609@informix.com>
Distribution: comp
Organization: Locus Computing Corp, Los Angeles
Lines: 45

In article <1990Nov8.021252.27609@informix.com> randall@informix.com (Randall Rhea) writes:
>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:
>

Keep in mind that this SQL is non-standard... There is no way in ANSI SQL
or most other mainstream SQLs such as DB2 or RDB to update a column using
a Subselect... the ANSI spec (that is ANSI SQL1, not SQL2 or SQL3) 
requires a value as the right side of an update assignment... In those
types of SQLs, the only way to do this is to use either:
  
  1) a single cursor that joins the two tables and then issues
     regular single row update statements for each row assigning
     the appropraite value to l_num; or

  2) a cursor that retrieves the main table one row and a time, and
     then issues a singleton Select to get the matching row in the
     secondary table and does an Update Where Current Of... on each
     row in the main table; or
  
  3) a cursor that retrieves the main table one row at a time, and
     a second cursor the retrieves the secondary table one row at
     a time, each in the same sequence, and do a stepped "join" to
     find matching rows and update the main table with a Where 
     Current Of...

I have done *NOTHING* to test the effectiveness of these solutions...
Understand that they require embedded SQL and can not be done from
a normal interactive SQL environment...
 
Jon Rosen