Yes, you right and have seen what happens. Indeed, does less update should say less updates to 1. Indeed, the clause "QR3PARAMS.ED = 1 is superfluous. I can see that in the second one the problem is with the update to null. All fixed now.
RBS On Wed, Nov 13, 2019 at 5:01 PM Keith Medcalf <kmedc...@dessus.com> wrote: > > Both queries update all rows in QR3PARAMS since there is no WHERE clause > to limit which rows are updated, so when you say "does less updates" what > do you mean, since it is manifestly impossible for one to do less updates > than the other -- both update every row or the table. > > Secondly, in the first update, the subquery is only executed if > QR3PARAMS.ED != 1 so therefore the clause "QR3PARAMS.ED = 1" in the where > clause of the subquery will always be false and is therefore meaningless > and serves only to consume CPU to no effect. > > The difference between them is that the second one will update the > QR3PARAMS to NULL if there are no CURRENT_MED rows with an ID = > QR3PARAMS.ID even if QR3PARAMS.ED = 1, while in the first one this will > not occur (QR3PARAMS.ED will be set to 1). > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > >-----Original Message----- > >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On > >Behalf Of Bart Smissaert > >Sent: Wednesday, 13 November, 2019 04:41 > >To: General Discussion of SQLite Database <sqlite- > >us...@mailinglists.sqlite.org> > >Subject: [sqlite] Why do these 2 updates give different results? > > > >UPDATE QR3PARAMS SET ED = > >CASE WHEN ED = 1 THEN 1 > >ELSE > >(SELECT 1 FROM CURRENT_MED WHERE > >(QR3PARAMS.ED = 1 OR TERM_TEXT GLOB 'Sildenafil*' OR TERM_TEXT GLOB > >'Tadalafil*' OR TERM_TEXT GLOB 'Vardenafil*') > >AND ID = QR3PARAMS.ID LIMIT 1) END > > > >UPDATE QR3PARAMS SET ED = > >(SELECT 1 FROM CURRENT_MED WHERE > >(QR3PARAMS.ED = 1 OR TERM_TEXT GLOB 'Sildenafil*' OR TERM_TEXT GLOB > >'Tadalafil*' OR TERM_TEXT GLOB 'Vardenafil*') > >AND ID = QR3PARAMS.ID LIMIT 1) > > > >Number 1 seems the most logical one and gives me the right answer. > >Number 2 does less updates, but not sure why this should be. > > > >RBS > >_______________________________________________ > >sqlite-users mailing list > >sqlite-users@mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
