Path: utzoo!yunexus!geac!syntron!jtsv16!uunet!seismo!sundc!pitstop!sun!amdcad!ames!pasteur!ucbvax!hplabs!nsc!voder!cullsj!gupta
From: gupta@cullsj.UUCP (Yogesh Gupta)
Newsgroups: comp.databases
Subject: Re: replace in SQL
Summary: Can't be done.
Keywords: replace, SQL
Message-ID: <445@cullsj.UUCP>
Date: 25 Oct 88 18:00:11 GMT
Article-I.D.: cullsj.445
References: <226@ur-cc.UUCP> <1272@cod.NOSC.MIL>
Organization: Cullinet Software, San Jose, CA
Lines: 50

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?
-- 
Yogesh Gupta                    | If you think my company will let me
Cullinet Software, Inc.         | speak for them, you must be joking.