vacca@burdvax.UUCP (Dave Vacca) (09/26/86)
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
ark@ut-sally.UUCP (Arthur M. Keller) (09/30/86)
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
hoffman@hdsvx1.UUCP (Richard Hoffman) (10/02/86)
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)