updating table rows in postgres using subquery
Postgres allows: This syntax is not standard SQL, but it is much more convenient for this type of query than standard SQL. I believe Oracle (at least) accepts something similar.
Postgres allows: This syntax is not standard SQL, but it is much more convenient for this type of query than standard SQL. I believe Oracle (at least) accepts something similar.
Taken from the MySQL manual (13.2.10.11 Rewriting Subqueries as Joins): A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone. So subqueries can be slower than LEFT [OUTER] JOIN, but in my opinion their strength … Read more
What is a LATERAL join? The feature was introduced with PostgreSQL 9.3. The manual: Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.) Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key … Read more
Technically, to repair your statement, you can add LIMIT 1 to the subquery to ensure that at most 1 row is returned. That would remove the error, your code would still be nonsense. Practically, you want to match rows somehow instead of picking an arbitrary row from the remote table store to update every row of your local table customer.Your rudimentary question … Read more
Update: This answer covers the general error classification. For a more specific answer about how to best handle the OP’s exact query, please see other answers to this question In MySQL, you can’t modify the same table which you use in the SELECT part.This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html Maybe you can just join the table … Read more
Update: This answer covers the general error classification. For a more specific answer about how to best handle the OP’s exact query, please see other answers to this question In MySQL, you can’t modify the same table which you use in the SELECT part.This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html Maybe you can just join the table … Read more
Every derived table (AKA sub-query) must indeed have an alias. I.e. each query in brackets must be given an alias (AS whatever), which can the be used to refer to it in the rest of the outer query. In your case, of course, the entire query could be replaced with:
You need to alias the subquery. or to be more explicit