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 = QR3PARAMS.ID LIMIT 1) END
>
> UPDATE QR3PARAMS SET ED =
> (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)
>
> Number 1 seems the most logical one and gives me the right answer.
> Number 2 does less updates, but not sure why this should be.
They both give me the same answer. But, I don't know your data. However, I
have a question: Why is it that when the UPDATE does not match 1, ED gets set
to ''?
create table QR3PARAMS (ID INTEGER PRIMARY KEY, ED);
create table CURRENT_MED (ID INTEGER PRIMARY KEY, TERM_TEXT);
insert into CURRENT_MED (TERM_TEXT) values ('Sildenafilame');
insert into CURRENT_MED (TERM_TEXT) values ('Tadalafilemete');
insert into CURRENT_MED (TERM_TEXT) values ('Vardenafilatala');
insert into CURRENT_MED (TERM_TEXT) values ('TTTTT');
insert into CURRENT_MED (TERM_TEXT) values ('aaaaaaa');
insert into QR3PARAMS (ED) values (0);
insert into QR3PARAMS (ED) values (0);
insert into QR3PARAMS (ED) values (0);
insert into QR3PARAMS (ED) values (1);
insert into QR3PARAMS (ED) values (0);
select * from QR3PARAMS;
select * from CURRENT_MED;
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;
SELECT changes();
select * from QR3PARAMS;
1|1
2|1
3|1
4|1
5|
Why is ED changed to '' or NULL for ID 5?
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
