Path: utzoo!utgpu!water!watmath!clyde!att!osu-cis!tut.cis.ohio-state.edu!ukma!nrl-cmf!ames!amdcad!sun!pitstop!robv
From: robv@pitstop.UUCP (Rob Vollum)
Newsgroups: comp.databases
Subject: Re: replace in SQL
Keywords: replace, SQL
Message-ID: <250@pitstop.UUCP>
Date: 27 Oct 88 15:46:28 GMT
References: <226@ur-cc.UUCP> <1272@cod.NOSC.MIL> <445@cullsj.UUCP>
Reply-To: robv@pitstop.UUCP (Rob Vollum)
Organization: Sun Microsystems, Inc., Lexington, MA
Lines: 52

In article <445@cullsj.UUCP] gupta@cullsj.UUCP (Yogesh Gupta) writes:
]In article <1272@cod.NOSC.MIL], dberg@cod.NOSC.MIL (David I. Berg) writes:
]] In article <226@ur-cc.UUCP], mpip@uhura.cc.rochester.edu (Robert Annechiarico) writes:
]] ] 
]] ] ..... I want to replace "score" in "result" with the value of "test" 
]] ] from "new", wherever "id" matches.
]] ]     In quel I would do:
]] ] 	replace result (score = new.test)
]] ] 		where result.id = new.id
]] ]     How do I do this in sql?
]] ] 
]] update result set score = (select test from new where new.id = score.id)
]
]For the above SQL statement to work, the result of the sub-select (the one in
]the parenthesis) has to return only one value.  Thus, it will not work.  What
]Robert would like to do is given by the following (contrived) example:
]
]	Given the following tables:
]
]        result                          new
]       ==============                  =============
]       |  id|  score|                  |  id|  test|
]	--------------                  -------------
]	|   1|     20|                  |   1|    90|
]	|   1|     30|                  |   2|    80|
]	|   2|     40|                  =============
]	|   2|     50|
]	|   3|     60|
]	==============
]
]	The update should result in:
]
]        result                          new
]       ==============                  =============
]       |  id|  score|                  |  id|  test|
]	--------------                  -------------
]	|   1|     90|                  |   1|    90|
]	|   1|     90|                  |   2|    80|
]	|   2|     80|                  =============
]	|   2|     80|
]	|   3|     60|
]	==============
]
]Anyone know how this can be done using SQL?
]-- 

I believe that there is no really direct way to do this in SQL. I think that
you must define a view, with the appropriate stuff from each table, and do
updates that way. 

Rob
...sun!sunne!robv