Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!watmath!clyde!caip!rutgers!sri-spam!nike!ll-xn!adelie!axiom!linus!raybed2!applicon!hdsvx1!hoffman
From: hoffman@hdsvx1.UUCP (Richard Hoffman)
Newsgroups: net.database
Subject: Re: relational query problem
Message-ID: <814@hdsvx1.UUCP>
Date: Thu, 2-Oct-86 09:20:48 EDT
Article-I.D.: hdsvx1.814
Posted: Thu Oct  2 09:20:48 1986
Date-Received: Sat, 4-Oct-86 13:40:30 EDT
References: <2709@burdvax.UUCP>
Reply-To: hoffman@hdsvx1.UUCP (Richard hoffman)
Organization: Schlumberger HDS, Houston
Lines: 50

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.

Say you have the following tables:

	t1:				t2:

	|UKEY	|VALUE	|		|KEY	|TOTAL	|
	+-------+-------+		+-------+-------+
	|a	|0	|		|a	|1	|
	+-------+-------+		|a	|2	|
					|a	|3	|
					+-------+-------+

The query you pose leaves it unclear which value (1,2, or 3) you would like
t1.value to be updated with.  In fact, it's not just unclear to the DBMS
(Ingres, right?) it's unclear to anyone what action you would like to take
here.  Replace with the first value and ignore others?  Replace each time,
overriding preceding replacements?

The fact that the field in t2 is called "total" suggests that you might
want to total all the values for which t1.ukey = t2.key, in which case
the proper query is:

	replace t1 (value = sum(t2.total by t2.key where t2.key = t1.key))

If your situation is such that, for each unique value of t2.key, t2.total
has the same value (that is, t2.total = "1", say, for all t2.key = "a"),
then you should normalize your tables to reflect this, and then run the
query you originally posed.  Or you could run the query I list above
replacing "sum" with "max" or "min".
-- 
 Richard Hoffman | "They sought it with thimbles, they sought it with care,
 Schlumberger WS | They pursued it with forks and hope;
 hdsvx1!hoffman  | They threatened its life with a railway share,
 713-928-4750    | They charmed it with smiles and soap."         (L. CARROLL)