On Sat, 16 Jan 2016 10:18:28 +0200
audio muze <audiomuze at gmail.com> wrote:
> Why is it that SQLite does not support a FROM clause in an update
> statement?
I can't answer why, but I can tell you it's fraught with potential
error. Inventing syntax runs the risk of supporting undesirable
behavior.
SQL Server has such a syntax. Unfortunately, when you say:
update T ... from S
the result is nondeterministic if more than one row in S matches. The
update applies all rows in S matching T. Of course, only the last one
is preserved. Of course, because order is nonsemantic, there's no way
to know *which* S row will "win".
The ANSI standard syntax
> UPDATE target
> SET resource = (SELECT resource
> FROM source
> WHERE id = target.id)
has the merit that the sub-select must produce 1 row. If it does not,
the server raises an error to the effect that SET requires a scalar
input.
--jkl
