On Wednesday, 13 November, 2019 13:26, Bart Smissaert
<[email protected]> 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 <[email protected]>
>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
>> [email protected]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
