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

Reply via email to