3 ms·
>> 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
by xpil 4y ago
>> 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.
- eatonphil 4y ago> 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).
- xpil 4y ago> Which don't? Redshift (a PostgreSQL derivative, more or less) can run an INSERT with a join, but cannot do an UPDATE with a join.
- wvenable 4y agoI'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.
- jamwt 4y agoArticle author here, yep, that was the intended point. Agree the wording was unclear, and there's a footnote now clarifying that. Thanks!
- wvenable 4y agoOne could argue that one statement vs. many statements isn't particularly important and as long as you have transactions.
- mkleczek 4y agoYou can in PostgreSQL and it is very useful: https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING https://www.postgresql.org/docs/current/queries-with.html#QU...