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
