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
