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

[Eric Abrahamsen <eric@ericabrahamsen.net>]

Stefan Nobis <stefan-ml@snobis.de> writes:

> Eric Abrahamsen <eric@ericabrahamsen.net> writes:
>
>> Okay, it's up. If anyone wants to explain to me the point of the
>> "where exists" clause in the SQL, I would be interested to hear. It
>> works as expected this way, but is that clause necessary?
>
> Yes, very necessary. Without it, all ratings would be changed - the
> two example rows without ratings (ids 5 and 12) would get the values
> from the intermediary org table, every other row in table bookreview
> would get its rating attribute set to null (because there is no
> matching entry in the temporary updates table).
>
> Remember: update without a where clause always touches every single
> row of the complete table.
>
> The "where exists" clause ensures that only those rows of bookreviews
> are touched that are present in the intermediary org table. If you do
> not like "where exists" you could say "where bookreview.id in (select
> id from udpates)".

Beautiful, this explains it perfectly. I had the sense that was the
purpose, but my very straightforward programming brain was insisting
that that job should be done with something like your final tip above:
update only if the id is in the updates table.

I was confused in part because the "where exists (select *..." looks
like its main purpose is to return rows. Will the select subquery
actually restrict the values that are available for updating/comparison
in the update statement? 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? Ie, we could write "where exists (select <foo>" to
the same effect? Hope that's clear!

In essence, the "where exists" is acting as an "inner join"...

Sorry for the off-topic SQL detour! And thanks very much for this cogent
explanation.

Eric