Bart Smissaert, on Wednesday, November 13, 2019 12:22 PM, wrote... > > 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.
Actually, they both update to null: 11:03:59.98>sqlite3 SQLite version 3.30.0 2019-10-04 15:03:17 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table QR3PARAMS (ID INTEGER PRIMARY KEY, ED); sqlite> create table CURRENT_MED (ID INTEGER PRIMARY KEY, TERM_TEXT); sqlite> insert into CURRENT_MED (TERM_TEXT) values ('Sildenafilame'); sqlite> insert into CURRENT_MED (TERM_TEXT) values ('Tadalafilemete'); sqlite> insert into CURRENT_MED (TERM_TEXT) values ('Vardenafilatala'); sqlite> insert into CURRENT_MED (TERM_TEXT) values ('TTTTT'); sqlite> insert into CURRENT_MED (TERM_TEXT) values ('aaaaaaa'); sqlite> insert into QR3PARAMS (ED) values (0); sqlite> insert into QR3PARAMS (ED) values (0); sqlite> insert into QR3PARAMS (ED) values (0); sqlite> insert into QR3PARAMS (ED) values (1); sqlite> insert into QR3PARAMS (ED) values (0); sqlite> select * from QR3PARAMS; 1|0 2|0 3|0 4|1 5|0 sqlite> select * from CURRENT_MED; 1|Sildenafilame 2|Tadalafilemete 3|Vardenafilatala 4|TTTTT 5|aaaaaaa sqlite> 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; sqlite> SELECT changes(); 5 sqlite> select * from QR3PARAMS; 1|1 2|1 3|1 4|1 5| Null value above on ID 5. sqlite> drop table QR3PARAMS; sqlite> drop table CURRENT_MED; sqlite> sqlite> create table QR3PARAMS (ID INTEGER PRIMARY KEY, ED); sqlite> create table CURRENT_MED (ID INTEGER PRIMARY KEY, TERM_TEXT); sqlite> insert into CURRENT_MED (TERM_TEXT) values ('Sildenafilame'); sqlite> insert into CURRENT_MED (TERM_TEXT) values ('Tadalafilemete'); sqlite> insert into CURRENT_MED (TERM_TEXT) values ('Vardenafilatala'); sqlite> insert into CURRENT_MED (TERM_TEXT) values ('TTTTT'); sqlite> insert into CURRENT_MED (TERM_TEXT) values ('aaaaaaa'); sqlite> insert into QR3PARAMS (ED) values (0); sqlite> insert into QR3PARAMS (ED) values (0); sqlite> insert into QR3PARAMS (ED) values (0); sqlite> insert into QR3PARAMS (ED) values (1); sqlite> insert into QR3PARAMS (ED) values (0); sqlite> select * from QR3PARAMS; 1|0 2|0 3|0 4|1 5|0 sqlite> select * from CURRENT_MED; 1|Sildenafilame 2|Tadalafilemete 3|Vardenafilatala 4|TTTTT 5|aaaaaaa sqlite> sqlite> 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); sqlite> SELECT changes(); 5 sqlite> select * from QR3PARAMS; 1|1 2|1 3|1 4|1 5| Null value again on ID 5 above. > On Wed, Nov 13, 2019 at 5:01 PM Keith Medcalf, on > > > > > 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, on > > >Behalf Of Bart Smissaert > > >Sent: Wednesday, 13 November, 2019 04:41 > > >To: General Discussion of SQLite Database, on > > >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users