From mboxrd@z Thu Jan 1 00:00:00 1970 From: Eric Abrahamsen Subject: Re: Finally figuring out some ob-sqlite stuff -- for worg? Date: Sat, 09 Nov 2019 09:58:58 -0800 Message-ID: <87r22h53tp.fsf@ericabrahamsen.net> References: <87bltn5it6.fsf@ericabrahamsen.net> <87d0e23lca.fsf@ucl.ac.uk> <87woc956zj.fsf@ericabrahamsen.net> Mime-Version: 1.0 Content-Type: text/plain Return-path: Received: from eggs.gnu.org ([2001:470:142:3::10]:36122) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1iTV15-0006I2-Gr for emacs-orgmode@gnu.org; Sat, 09 Nov 2019 12:59:21 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1iTV11-0004qg-IJ for emacs-orgmode@gnu.org; Sat, 09 Nov 2019 12:59:18 -0500 Received: from 195-159-176-226.customer.powertech.no ([195.159.176.226]:57824 helo=blaine.gmane.org) by eggs.gnu.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:32) (Exim 4.71) (envelope-from ) id 1iTV0z-0004m3-Hk for emacs-orgmode@gnu.org; Sat, 09 Nov 2019 12:59:13 -0500 Received: from list by blaine.gmane.org with local (Exim 4.89) (envelope-from ) id 1iTV0w-000DYB-BN for emacs-orgmode@gnu.org; Sat, 09 Nov 2019 18:59:10 +0100 List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: "Emacs-orgmode" To: emacs-orgmode@gnu.org Stefan Nobis writes: > Eric Abrahamsen 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 " 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