Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!dciem!nrcaer!cognos!garyp
From: garyp@cognos.UUCP (Gary Puckering)
Newsgroups: comp.databases
Subject: Re: need help with sql problem (informix)
Message-ID: <468@cognos.UUCP>
Date: Wed, 25-Mar-87 10:10:39 EST
Article-I.D.: cognos.468
Posted: Wed Mar 25 10:10:39 1987
Date-Received: Sat, 28-Mar-87 01:21:08 EST
References: <276@jpusa1.UUCP>
Reply-To: garyp@smokey.UUCP (Gary Puckering)
Organization: Cognos Incorporated, Ottawa, Canada
Lines: 64

In article <276@jpusa1.UUCP> stu@jpusa1.UUCP (Stu Heiss) writes:
>I'm trying to do what should be a simple update and can't believe the
>abysmal performance.  Maybe I'm missing something obvious.  The scenario
>is a 3 table database (name,address,accounts) and I'm updating a batch
>from a fourth table.  Here's the code to do the name:
>	update name_table
>		set name = (select name from change_table where
>			name_table.id = change_table.id)
>		where name_table.id in
>			(select id from change_table);
>This seems overly complex when all I want to do is find the entries in
>the name_table whose id match those in the change_table and update the
>name column (field).  

Wow!  This is scarey!  I hope someone can come up with an elegant SQL
request for this.  I can't think of one.  However, I can offer a suggestion
and a comment:

1.  You *must* have an index on the id column of change_table.  Otherwise,
	a sequential scan of change_table will be required for every row
	read from name_table.

2.  The strategy SQL has forced you into is a bad one.  You should be
	able to drive the update request from the change_table, rather than
	from the name_table.  For examplem, in QTP, our 4GL transaction
	processor, you would code this request as:

		access change_table link to name_table
		output name_table update
		  item name final name of change_table
        go

    For this request to execute efficiently, their need only be an index
    on id of name_table.  Essentially, this request does a natural join of
    change_table and name_table and then it updates only the name_table row.

    Unfortunately, I can't see any way to do this in interactive SQL.

3.  This above strategy could be accomplished within a program by using
    cursor.  For example:

		sql declare get_changes cursor for
		  select id.change_table name.change_table
		  into :xid :xname
		  from change_table, name_table
		  where id.change_table = id.name_table

    Now you can code a loop which fetches the appropriate id's and names
	into host variables xid and xname.  Within the loop you code an SQL
	update request like:

		sql update name_table
		  set name = :xname
		  where id = :xid


Like I say, I hope someone else can come up with a decent solution which
can be coded in interactive SQL.  If not, it just goes to show you that
SQL is a lousy excuse for a 4GL.  It leads to counter-intuitive requests.

-- 
Gary Puckering        3755 Riverside Dr.
Cognos Incorporated   Ottawa, Ontario       decvax!utzoo!dciem!
(613) 738-1440        CANADA  K1G 3N3       nrcaer!cognos!garyp