Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!watmath!clyde!caip!lll-crg!seismo!ut-sally!ark
From: ark@ut-sally.UUCP (Arthur M. Keller)
Newsgroups: net.database
Subject: Re: relational query problem
Message-ID: <5842@ut-sally.UUCP>
Date: Tue, 30-Sep-86 10:26:13 EDT
Article-I.D.: ut-sally.5842
Posted: Tue Sep 30 10:26:13 1986
Date-Received: Wed, 1-Oct-86 05:59:33 EDT
References: <2709@burdvax.UUCP>
Reply-To: ark@sally.utexas.edu.UUCP (Arthur M. Keller)
Organization: U. Texas CS Dept., Austin, Texas
Lines: 37
Keywords: one-to-many join replace

In article <2709@burdvax.UUCP> vacca@burdvax.UUCP (Dave Vacca) writes:
>I tried the following query on a relational DBMS.  It resulted in
>an "ambiguous replace - more than one update on same row" error.
>I presume I am doing this incorrectly.  What I need is the proper
>method for doing the following:
>
>I have two tables: t1 and t2.  t1 has a unique key.  t2 also has
>a key, but not a unique one.  I am joining the two tables with
>each table's key, i.e., where t1.UKEY = t2.KEY .  Actually, the 
>complete operation looks like the following:
>
>replace t1 ( value = t2.total ) where t1.UKEY = t2.KEY
>
>The join is one-to-many (t1-to-t2).  Thus, there may be 3 rows in t2
>which have the same key value as just 1 row in t1.  There will never
>be 2 or more rows in t1 which have the same key value as 1 row in t2.
>
>Do I have to normalize t2?
>
>---------------------------------------
>
>Dave Vacca
>Burroughs Corporation
>..!burdvax!asgdtn!vacca


The problem is that a t1 tuple is updated repeatedly for each matching
t2 tuple.  Rather, you need to use some GROUP BY operator to aggregate
the matching t2 tuples.  In general, whenever you use an aggregate function
you need to indicate the grouping of the aggregation using a GROUP BY.

Arthur Keller

-- 
------------------------------------------------------------------------------
Arpanet: ARK@SALLY.UTEXAS.EDU
UUCP:    {gatech,harvard,ihnp4,pyramid,seismo}!ut-sally!ark