Description
As of cockroachdb/cockroach#39202, we support UPDATE ... FROM queries. There is this really old and long issue that tracks its progress cockroachdb/cockroach#7841 that you might find helpful. Its a feature thats been requested for some time now and it closes some postgres compatibility gaps. Now that we have this we can also add support of DELETE ... USING which has the same semantics.
The guarantees closely resemble the postgres feature itself: https://www.postgresql.org/docs/9.5/sql-update.html
Another important note that we should call out is 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.