Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Bart Smissaert
This looks to work fine and runs fast as well, combining the 2 queries I have now: UPDATE QR3PARAMS SET ED = CASE WHEN ID IN (SELECT ID FROM PROBLEMS WHERE READ_CODE GLOB 'Eu522*' OR READ_CODE GLOB 'E2273*' OR READ_CODE = '1777409015') OR ID IN (SELECT ID FROM CURRENT_MED WHERE TERM_TEXT GLOB

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Peter da Silva
> > This almost seems like a job for a view. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Bart Smissaert
Actually, I still have 2 queries as before the one mentioned I have: UPDATE QR3PARAMS SET ED = (SELECT 1 FROM PROBLEMS WHERE (READ_CODE GLOB 'Eu522*' OR READ_CODE GLOB 'E2273*' OR READ_CODE = '1777409015') AND ID = QR3PARAMS.ID LIMIT 1) But I thought to keep matters simple (and maybe a bit

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Keith Medcalf
On Wednesday, 13 November, 2019 13:26, Bart Smissaert wrote: >Thanks, the second one does the job as I need 1 or 0 and no nulls. >It saves me running 2 queries as before had: >UPDATE QR3PARAMS SET ED = CASE WHEN ED = 1 THEN 1 ELSE >(SELECT 1 FROM CURRENT_MED WHERE >(TERM_TEXT GLOB

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Bart Smissaert
Thanks, the second one does the job as I need 1 or 0 and no nulls. It saves me running 2 queries as before had: UPDATE QR3PARAMS SET ED = CASE WHEN ED = 1 THEN 1 ELSE (SELECT 1 FROM CURRENT_MED WHERE (TERM_TEXT GLOB 'Sildenafil*' OR TERM_TEXT GLOB 'Tadalafil*' OR TERM_TEXT GLOB 'Vardenafil*')

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread David Raymond
Why not change it to something along the lines of: UPDATE QR3PARAMS SET ED = 1 WHERE ED is not 1 AND EXISTS ( SELECT 1 FROM CURRENT_MED WHERE ID = QR3PARAMS.ID AND ( TERM_TEXT GLOB 'Sildenafil*' OR TERM_TEXT GLOB 'Tadalafil*' OR TERM_TEXT GLOB 'Vardenafil*' ) );

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Jose Isaias Cabrera
Donald Griggs, on Wednesday, November 13, 2019 12:26 PM, wrote... > > > > > When dealing with ED and sildenafil, getting a NULL result disappoints > > everyone. ;-) > > Good one! NOT that **I** know anything about that. Hahahahah... ___

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Jose Isaias Cabrera
AMS.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. > > > > >-

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Donald Griggs
> > When dealing with ED and sildenafil, getting a NULL result disappoints > everyone. ;-) > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Bart Smissaert
> 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: Gene

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Keith Medcalf
ot 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 us...@mailinglists.sqlite.org> >Subject: [sqlite] Why do these 2 updates give diff

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Bart Smissaert
My data is the same, except ED has affinity integer and term_text affinity text. RBS On Wed, 13 Nov 2019, 15:25 Jose Isaias Cabrera, wrote: > > Bart Smissaert, on Wednesday, November 13, 2019 06:41 AM, wrote... > > > > UPDATE QR3PARAMS SET ED = > > CASE WHEN ED = 1 THEN 1 > > ELSE > > (SELECT

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Jose Isaias Cabrera
David Raymond, on Wednesday, November 13, 2019 10:34 AM, wrote... > > "Why is ED changed to '' or NULL for ID 5?" > > When you update to a subquery which returns no rows, then the field gets > updated to null. So, I have to address "no matches or no rows returned" in another subquery. Huh!

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread David Raymond
"Why is ED changed to '' or NULL for ID 5?" When you update to a subquery which returns no rows, then the field gets updated to null. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Jose Isaias Cabrera
Bart Smissaert, on Wednesday, November 13, 2019 06:41 AM, wrote... > > 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 =

[sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Bart Smissaert
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