mpip@uhura.cc.rochester.edu (Robert Annechiarico) (10/24/88)
Hi,
We're migrating from quel to sql, and have had trouble figuring out
how to update a relation when we want the values to come from a second
relation. HELP! Please...
Suppose that I have two relations, "result" and "new". Each has a key
"id". The "result" relation has a second field named "score". The "new"
relation has a second field named "test". 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?
Related question: what sql tutorial would people recommend? I've got
the Ingres SQL reference guide, and Date's A Guide to Ingres and An Introduction
to Database System; none of them showed how to do the required update in
sql.
Thanks very much.
Eric Carleen
University of Rochester Medical Center
(716)-275-5391
Bitnet: heartedc@uorhbv
UUCP: {ames,cmcl2,decvax,rutgers}!rochester!ur-msbvax!ur-hbv!heartedc
Internet: mpip@uhura.cc.rochester.edu
"The large print giveth, and the small print taketh away". - Tom Waitsdberg@cod.NOSC.MIL (David I. Berg) (10/25/88)
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) -- 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
gupta@cullsj.UUCP (Yogesh Gupta) (10/26/88)
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.
dberg@cod.NOSC.MIL (David I. Berg) (10/26/88)
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
jkrueger@daitc.daitc.mil (Jonathan Krueger) (10/26/88)
In article <1272@cod.NOSC.MIL>, dberg@cod (David I. Berg) writes: >update result set score = (select test from new where new.id = score.id) > >-- Which SQL is executing this? And has its vendor explained that ANSI compliant SQL will not do this? -- Jon Ref to Date, "A Guide to the SQL Standard". --
jkrueger@daitc.daitc.mil (Jonathan Krueger) (10/27/88)
In article <1278@cod.NOSC.MIL>, dberg@cod (David I. Berg) writes: >update result set score = (select test from new where result.id = new.id) > where result.id in (select id from new) What SQL is executing this? Has its vendor informed you that such SQL is not standard and hence will not execute on ANSI compliant SQL DMBS? References: [1] C. J. Date, "An Introduction to Database Systems", Volume I, Fourth Edition, Addison-Wesley, 1986, Section 10.4, Example 10.4.5, Updating one table from another: "...This update cannot be done in SQL in a single statement". [2] C. J. Date, "A Guide to the SQL Standard", Addison-Wesley, 1987, Appendix B, A SQL Grammar: update-statement-searched ::= UPDATE table SET assignment-commalist [ where-clause ] foo-commalist is informally described in Section 3.5 of the book as (foo, foo, foo) assignment ::= column = { scalar-exp | NULL } Scalar expressions and query (SELECT) expressions are disjoint sets. Therefore, you cannot update a table with values generated from a query expression in standard SQL. Seems like a very reasonable enhancement, but the ANSI standard does not support it. -- Jon --
robv@pitstop.UUCP (Rob Vollum) (10/27/88)
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
dberg@cod.NOSC.MIL (David I. Berg) (10/28/88)
In article <215@daitc.daitc.mil>, jkrueger@daitc.daitc.mil (Jonathan Krueger) writes: > In article <1278@cod.NOSC.MIL>, dberg@cod (David I. Berg) writes: > >update result set score = (select test from new where result.id = new.id) > > where result.id in (select id from new) > > What SQL is executing this? Has its vendor informed you that such SQL > is not standard and hence will not execute on ANSI compliant SQL DMBS? > This statement is being executed by INFORMIX SQL Version 2.1. I will gladly, upon request, provide a complete SQL script to create the tables in question with the values suggested by Mr. Gupta and this update statement to achieve the desired result. -- 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