sql: support UPDATE ... FROM

[maddyblue]

(edit @knz 2018-05-19)

UPDATE X SET ... = Y FROM Z
https://www.postgresql.org/docs/10/static/sql-update.html

performs (I think) a lateral join of X with Z and then makes the values computed by Z available for the scalar expressions in Y, to serve as values to update.

Full support for the feature needs support for correlated subqueries #3288 and lateral joins #24560.

Reduced support when the relational expression Z is uncorrelated (a common case) could perhaps be added with a simple join, although additional execution machinery is still needed to propagate the PK prefixes for the X operand to the join through up to the point UPDATE can pick them up.

As to which rows get updated:

• Rows of table X that don't match in the join are not updated.
• Rows that match in X with the join on Z are updated. If there is just 1 matching row (or if there is a cross join but the right operand has just 1 row), then the situation is clear. I am not exactly sure what happens if there are multiple rows.

The work to be done is twofold:

• construct (and properly optimize) the data source with a join when FROM is specified
• plumb the PK values properly so that UPDATE still has PKs to work with.

[maddyblue]

See #7247 when fixing this issue.

[aitjcize]

any updates on when this will be supported?

[dianasaur323]

cc @awoods187

[awoods187]

@aitjcize we will not be including this in our upcoming April release. I'd love to hear more about your use case to understand your need for this feature. This will help us prioritize for future roadmap inclusion

[aitjcize]

For our specific use case, we simply want to save as much query as possible. We are using UPDATE table SET ... FROM table old_table WHERE ... RETURNING old_table.X to return pre-update value after update.

[jviksne]

I would also like to vote for this to be implemented. I need to do some conditional operations on a sum of values from child records. In PostgreSQL I can do it in a single query, while I guess in CockroachDB I will need to split it into multiple separate ones.

The query looks something like this (with simplified conditions here):
UPDATE my_table SET child_cost=
CASE WHEN subq.child_cost_sum < cost THEN subq.child_cost_sum ELSE cost END
FROM (SELECT sum(cost) AS child_cost_sum FROM my_table WHERE parent_id=123) AS subq;

[knz]

I have extended the top description of the issue to detail the work that needs to be done. It's not super simple but it's not fundamentally difficult either. Probably reasonable to table for CRDB 2.2. (edit 2018-10-19 I don't know actually)

[awoods187]

top requested feature based on telemetry data

[knz]

Discussed this with @andy-kimball yesterday - Andy confirmed that once the opt framework knows about mutations, planning update...from will be a natural extension of the support for correlated subqueries and lateral joins.

(Of course ensuring the statement is recognized in the first place will still require some extra work)

[knz]

Example use on the forum: https://forum.cockroachlabs.com/t/how-to-update-one-table-from-another/2217

[justinj]

https://www.postgresql.org/docs/9.5/sql-update.html

Re: what happens if multiple rows match:

When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join.