[xpil]

>> 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.

[wvenable]

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.

[mkleczek]

You can in PostgreSQL and it is very useful: https://www.postgresql.org/docs/current/queries-with.html#QU...

[jamwt]

Article author here, yep, that was the intended point. Agree the wording was unclear, and there's a footnote now clarifying that. Thanks!

[wvenable]

One could argue that one statement vs. many statements isn't particularly important and as long as you have transactions.

[eatonphil]

> 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]

> 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.