My data is the same, except ED has affinity integer and term_text affinity
text.
RBS
On Wed, 13 Nov 2019, 15:25 Jose Isaias Cabrera, <jic...@outlook.com> wrote:
>
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
