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