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 faster) I would split it in 2. RBS On Wed, Nov 13, 2019 at 8:39 PM Keith Medcalf <kmedc...@dessus.com> wrote: > > On Wednesday, 13 November, 2019 13:26, Bart Smissaert < > bart.smissa...@gmail.com> 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 'Sildenafil*' OR TERM_TEXT GLOB 'Tadalafil*' OR TERM_TEXT > >GLOB 'Vardenafil*') > >AND ID = QR3PARAMS.ID LIMIT 1) END > > You could of course have simply done this: > > UPDATE QR3PARAMS > SET ED = CASE WHEN ED = 1 THEN 1 ELSE > coalese((SELECT 1 > FROM CURRENT_MED > WHERE (TERM_TEXT GLOB 'Sildenafil*' OR > TERM_TEXT GLOB 'Tadalafil*' OR > TERM_TEXT GLOB 'Vardenafil*') > AND ID = QR3PARAMS.ID > LIMIT 1), 0) END; > > rather than the second update so as to coalesce nulls into 0 ... > > However the "set ed = exists(...) where ed is not 1" has the advantage > that it saves the extra couple of machine instructions required to coalesce > null to 0 and also only runs the correlated subquery when it needs to run > rather than for each record. > > >On Wed, Nov 13, 2019 at 7:21 PM David Raymond <david.raym...@tomtom.com> > >wrote: > > > >> 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*' > >> ) > >> ); > >> > >> which would leave ED alone if it didn't find anything, > >> or the following, which would always set it to true or false > >> > >> UPDATE QR3PARAMS > >> SET ED = 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*' > >> ) > >> ) > >> WHERE > >> ED is not 1 > >> ; > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > >> _______________________________________________ > >> 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
