Eric Abrahamsen <e...@ericabrahamsen.net> writes: > I was confused in part because the "where exists (select *..." looks > like its main purpose is to return rows.
Indeed that's the purpose: Restrict the set of rows upon which update acts on. Here I tried to reformat the statement a bit in order to emphasize its structure: #+begin_src sql UPDATE bookreview SET rating = (select rating from updates where bookreview.id = updates.id) WHERE EXISTS (select * from updates where updates.id = bookreview.id); #+end_src The subselect of the "SET rating" part is a correlated subquery. So if you imagine UPDATE as a kind of loop over the table, the subquery of the SET part is executed once for every row UPDATE acts on (maybe the SQL execution engine optimizes this in some kind, but the mental model here is: run the subquery for every row we visit on our journey throught the table). Only the WHERE EXISTS clause belonging directly to the UPDATE statement will reduce the set of rows to act on. > Will the select subquery actually restrict the values that are > available for updating/comparison in the update statement? No. > Or does the "exists" mean the subquery is treated as a plain yes/no > boolean, and the update still has access to anything it likes? We > could write "where exists (select <foo>" to the same effect? Yes. The SELECT clause of an EXISTS subquery (as in the above example) is rather meaningless. So somethimes you see constructs like "where exists (select 1 from ...)". Some SQL engines are not very clever and execute the subquery of such an EXISTS clause unchanged - meaning that way too much data is fetched for the intermediate result (unnecessary IO and maybe polluting caches). Thus the "select 1" as a workaround for those unclever engines. But current engines should have no problems with optimizing these EXISTS subqueries and in that case it does not matter how the select clause looks like - it will be ignored. > In essence, the "where exists" is acting as an "inner join"... Yes, effectively we are simulating an inner join at this point. Sadly, many SQL engines are not able to update rows of join constructs (or at least have quite severe constraints in these cases). Thus we need to build these kinds of workarounds to change data in more complex cases. SQL is quite a capable language, but it has also has some rough edges. :) -- Until the next mail..., Stefan.