+1 Very much a needed feature! - For inserting / modifying items at arrays, i think it might help to have another token after the ‘AT’ to denote that this is a position (it might just be me, but ‘AT 1’ seems a little too vague). Maybe ‘AT INDEX 1’? (given that INDEX is already a reserved word?) - The Change production seems like it should be separated with a comma (to really hammer in the point that this is a sequence) or even a semicolon if we want to make this more PL/SQL-like. It could also an opportunity to make the parenthesis optional, if you want to go down that route.
Other than those two minor things, I like it! Best, Glenn > On Oct 20, 2024, at 10:33, Mike Carey <dtab...@gmail.com> wrote: > > +1 for this (obviously, since I am on it). FYI, we have also run our UPDATE > user model and syntax by Yannis P (father of SQL++) and Don C (father of SQL) > for their input prior to posting this APE. :-) We've needed this feature > for quite some time in order to conveniently express small(-ish) changes to > arbitrary (possibly large) schema-less documents. > > Discussion welcome! > > Cheers, > > Mike > >> On 10/18/24 3:18 PM, Abhishek Jindal wrote: >> Hi All, >> >> I'm initiating a discussion thread proposing the SQL++ UPDATE statement in >> AsterixDB. >> *Feature:* Adding support for SQL++ UPDATE statement. >> *Details:* AsterixDB currently does not support UPDATE operations without >> having >> to pass an entire new object to replace an existing record in a collection. >> The following proposal discusses syntax and semantics of the UPDATE >> statement as part of >> SQL++ for AsterixDB. >> >> We plan to implement this feature by rewriting the UPDATE statement into its >> equivalent >> UPSERT form, allowing us to reuse the existing LSM-tree UPSERT machinery to >> handle the transformed incoming record. >> >> To apply transformations to an incoming record, we employ the following >> approach: >> >> 1. We recursively traverse the hierarchy of transformations as specified by >> the user in the query. >> 2. At each hierarchical level, we rewrite the transformation to the >> equivalent record-merge() built-in function. >> 3. These rewritten record-merge() transformations are then combined in a >> bottom-up manner, finally producing the final transformation function. >> >> APE >> :https://cwiki.apache.org/confluence/display/ASTERIXDB/APE+9%3A+UPDATE+Statement
