> Unlike SELECT, these operations don't feature JOINs or subqueries or any other magic that brings together tables.
This is a false statement. Both INSERT and UPDATE support JOINs and subqueries / CTEs. At least according to the standard - not every engine implementing them is another story.
I'm forgiving on the authors point here. If you have JOINs and subqueries, you're just doing a SELECT to get data that can only be UPDATEd/INSERTed on a single table. You can't do an INSERT across 5 tables in one statement.
not every engine implementing them is another story
Which don't? I'd have assumed anything inside of `SELECT`'s `FROM` would be allowed inside of `INSERT` and `UPDATE`.
Or maybe you're not saying you know there are implementations that have these restrictions just that any random implementation might not be there (yet).
Comments
This is a false statement. Both INSERT and UPDATE support JOINs and subqueries / CTEs. At least according to the standard - not every engine implementing them is another story.
I'm forgiving on the authors point here. If you have JOINs and subqueries, you're just doing a SELECT to get data that can only be UPDATEd/INSERTed on a single table. You can't do an INSERT across 5 tables in one statement.
You can in PostgreSQL and it is very useful: https://www.postgresql.org/docs/current/queries-with.html#QU...
Article author here, yep, that was the intended point. Agree the wording was unclear, and there's a footnote now clarifying that. Thanks!
One could argue that one statement vs. many statements isn't particularly important and as long as you have transactions.
Which don't? I'd have assumed anything inside of `SELECT`'s `FROM` would be allowed inside of `INSERT` and `UPDATE`.
Or maybe you're not saying you know there are implementations that have these restrictions just that any random implementation might not be there (yet).
Redshift (a PostgreSQL derivative, more or less) can run an INSERT with a join, but cannot do an UPDATE with a join.