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 And: UPDATE QR3PARAMS SET ED = 0 WHERE ED IS NULL RBS 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 > ; > _______________________________________________ > 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
