Path: utzoo!attcan!uunet!peregrine!elroy!ames!lll-tis!daitc!jkrueger@daitc.daitc.mil
From: jkrueger@daitc.daitc.mil (Jonathan Krueger)
Newsgroups: comp.databases
Subject: Re: replace in SQL
Keywords: replace, SQL
Message-ID: <215@daitc.daitc.mil>
Date: 26 Oct 88 17:52:50 GMT
References: <226@ur-cc.UUCP> <1272@cod.NOSC.MIL> <445@cullsj.UUCP> <1278@cod.NOSC.MIL>
Sender: jkrueger@daitc.daitc.mil
Reply-To: jkrueger@daitc.daitc.mil (Jonathan Krueger)
Organization: Defense Applied Information Technology Center
Lines: 34
In-reply-to: dberg@cod.NOSC.MIL (David I. Berg)

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
--