Path: utzoo!utgpu!water!watmath!clyde!att!osu-cis!tut.cis.ohio-state.edu!rutgers!ucsd!nosc!cod!dberg
From: dberg@cod.NOSC.MIL (David I. Berg)
Newsgroups: comp.databases
Subject: Re: replace in SQL
Summary: It CAN be done!
Keywords: replace, SQL
Message-ID: <1278@cod.NOSC.MIL>
Date: 26 Oct 88 01:40:09 GMT
References: <226@ur-cc.UUCP> <1272@cod.NOSC.MIL> <445@cullsj.UUCP>
Organization: Naval Ocean Systems Center, San Diego
Lines: 33

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.....
> > >     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 ... has to 
> return only one value.  Thus, it will not work.  ...Given the following 
> (contrived) example:
> 	Given the following tables: {detail omitted}
> 	The update should result in: {detail omitted}
> Anyone know how this can be done using SQL?

I confess to an error in my original response.   The where clause should
read "where new.id = result.id".  This statement works when for every 
value in result.id there is a corresponding value in new.id.  However, to 
solve the (more general) case given by Mr. Gupta, the statment must read 
as follows:

update result set score = (select test from new where result.id = new.id)
  where result.id in (select id from new)

Given Mr. Gupta's tables in their original state, this statement produces
the results he suggests should be produced.

-- 
David I. Berg (dberg@nosc.mil)
GENISYS Information Systems, Inc., 4250 Pacific Hwy #118, San Diego, CA 92110
MILNET: dberg@nosc.mil
UUCP:   {akgua decvax dcdwest ucbvax}!sdcsvax!noscvax!dberg