FROM clause in UPDATE statement

[Dimitar Misev]

It is not possible to perform UPDATE query on coll A based on the values of coll B, i.e. something like

UPDATE A SET A[..]
ASSIGN B
WHERE oid(B) = ..

What's missing in the syntax is support for specifying multiple collections, e.g.

UPDATE A SET A[..]
ASSIGN B
FROM B

Formally speaking, the solution should take the current "UPDATE" options and extend them using a "FROM" clause. This can be achieved by introducing syntax for the following grammar constructions:

Added UPDATE grammar:

updateExp : update iteratedCollection set updateSpec
assign generalExp
where generalExp
from collectionList

and

update iteratedCollection set updateSpec
assign generalExp
from collectionList

[Dimitar Misev]

Or maybe just assume there's a FROM clause implicitly like we do for A.

[Vlad Merticariu]

Currently the implicit FROM clause for A comes from the UPDATE itself:

UPDATE A as a
SET a ..

We could allow

UPDATE A as a,

B as b

SET a = b …

Both approaches are valid (​https://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match). Looking at the code, I think allowing several collections in the UPDATE clause is more straight forward to implement.

[Peter Baumann]

my first idea was a nested query, which is already discussed in stackoverflow as I see. If we don't want to use that (and it comes at a complexity price for the user) then I find the implicit FROM ok, in particular as it seems a lower hanging fruit.

[Peter Baumann]

of course, having a FROM clause would allow a clear indication of what is to be updated. But I'd see this as cream in the coffee given that the "UPDATE A, B" approach has precedent out there.