Stefan Nobis <stefan...@snobis.de> writes: > Eric Abrahamsen <e...@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