Hacker News Reader

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

View on HN

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.

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.

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

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

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

Reply on news.ycombinator.com