Re: Finally figuring out some ob-sqlite stuff -- for worg?

[Stefan Nobis <stefan-ml@snobis.de>]

Eric Abrahamsen <eric@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.