Why do these 2 updates give different results?

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

Why do these 2 updates give different results?

Bart Smissaert
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.

RBS
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do these 2 updates give different results?

Jose Isaias Cabrera-4

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do these 2 updates give different results?

David Raymond
"Why is ED changed to '' or NULL for ID 5?"

When you update to a subquery which returns no rows, then the field gets updated to null.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do these 2 updates give different results?

Jose Isaias Cabrera-4

David Raymond, on Wednesday, November 13, 2019 10:34 AM, wrote...
>
> "Why is ED changed to '' or NULL for ID 5?"
>
> When you update to a subquery which returns no rows, then the field gets updated to null.

So, I have to address "no matches or no rows returned" in another subquery.  Huh!  Muchas gracias.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do these 2 updates give different results?

Bart Smissaert
In reply to this post by Jose Isaias Cabrera-4
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, <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do these 2 updates give different results?

Keith Medcalf
In reply to this post by Bart Smissaert

Both queries update all rows in QR3PARAMS since there is no WHERE clause to limit which rows are updated, so when you say "does less updates" what do you mean, since it is manifestly impossible for one to do less updates than the other -- both update every row or the table.

Secondly, in the first update, the subquery is only executed if QR3PARAMS.ED != 1 so therefore the clause "QR3PARAMS.ED = 1" in the where clause of the subquery will always be false and is therefore meaningless and serves only to consume CPU to no effect.

The difference between them is that the second one will update the QR3PARAMS to NULL if there are no CURRENT_MED rows with an ID = QR3PARAMS.ID even if QR3PARAMS.ED = 1, while in the first one this will not occur (QR3PARAMS.ED will be set to 1).

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Bart Smissaert
>Sent: Wednesday, 13 November, 2019 04:41
>To: General Discussion of SQLite Database <sqlite-
>[hidden email]>
>Subject: [sqlite] Why do these 2 updates give different results?
>
>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.
>
>RBS
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do these 2 updates give different results?

Bart Smissaert
Yes, you right and have seen  what happens.
Indeed, does less update should say less updates to 1.
Indeed, the clause "QR3PARAMS.ED = 1 is superfluous.
I can see that in the second one the problem is with the update to null.
All fixed now.

RBS


On Wed, Nov 13, 2019 at 5:01 PM Keith Medcalf <[hidden email]> wrote:

>
> Both queries update all rows in QR3PARAMS since there is no WHERE clause
> to limit which rows are updated, so when you say "does less updates" what
> do you mean, since it is manifestly impossible for one to do less updates
> than the other -- both update every row or the table.
>
> Secondly, in the first update, the subquery is only executed if
> QR3PARAMS.ED != 1 so therefore the clause "QR3PARAMS.ED = 1" in the where
> clause of the subquery will always be false and is therefore meaningless
> and serves only to consume CPU to no effect.
>
> The difference between them is that the second one will update the
> QR3PARAMS to NULL if there are no CURRENT_MED rows with an ID =
> QR3PARAMS.ID even if QR3PARAMS.ED = 1, while in the first one this will
> not occur (QR3PARAMS.ED will be set to 1).
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-----Original Message-----
> >From: sqlite-users <[hidden email]> On
> >Behalf Of Bart Smissaert
> >Sent: Wednesday, 13 November, 2019 04:41
> >To: General Discussion of SQLite Database <sqlite-
> >[hidden email]>
> >Subject: [sqlite] Why do these 2 updates give different results?
> >
> >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.
> >
> >RBS
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do these 2 updates give different results?

Donald Griggs
>
> When dealing with ED and sildenafil, getting a NULL result disappoints
> everyone.     ;-)
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do these 2 updates give different results?

Jose Isaias Cabrera-4
In reply to this post by Bart Smissaert

Bart Smissaert, on Wednesday, November 13, 2019 12:22 PM, wrote...
>
> Yes, you right and have seen  what happens.
> Indeed, does less update should say less updates to 1.
> Indeed, the clause "QR3PARAMS.ED = 1 is superfluous.
> I can see that in the second one the problem is with the update to null.
> All fixed now.

Actually, they both update to null:

11:03:59.98>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table QR3PARAMS (ID INTEGER PRIMARY KEY, ED);
sqlite> create table CURRENT_MED (ID INTEGER PRIMARY KEY, TERM_TEXT);
sqlite> insert into CURRENT_MED (TERM_TEXT) values ('Sildenafilame');
sqlite> insert into CURRENT_MED (TERM_TEXT) values ('Tadalafilemete');
sqlite> insert into CURRENT_MED (TERM_TEXT) values ('Vardenafilatala');
sqlite> insert into CURRENT_MED (TERM_TEXT) values ('TTTTT');
sqlite> insert into CURRENT_MED (TERM_TEXT) values ('aaaaaaa');
sqlite> insert into QR3PARAMS (ED) values (0);
sqlite> insert into QR3PARAMS (ED) values (0);
sqlite> insert into QR3PARAMS (ED) values (0);
sqlite> insert into QR3PARAMS (ED) values (1);
sqlite> insert into QR3PARAMS (ED) values (0);
sqlite> select * from QR3PARAMS;
1|0
2|0
3|0
4|1
5|0
sqlite> select * from CURRENT_MED;
1|Sildenafilame
2|Tadalafilemete
3|Vardenafilatala
4|TTTTT
5|aaaaaaa
sqlite> 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;
sqlite> SELECT changes();
5
sqlite> select * from QR3PARAMS;
1|1
2|1
3|1
4|1
5|

Null value above on ID 5.

sqlite> drop table QR3PARAMS;
sqlite> drop table CURRENT_MED;
sqlite>
sqlite> create table QR3PARAMS (ID INTEGER PRIMARY KEY, ED);
sqlite> create table CURRENT_MED (ID INTEGER PRIMARY KEY, TERM_TEXT);
sqlite> insert into CURRENT_MED (TERM_TEXT) values ('Sildenafilame');
sqlite> insert into CURRENT_MED (TERM_TEXT) values ('Tadalafilemete');
sqlite> insert into CURRENT_MED (TERM_TEXT) values ('Vardenafilatala');
sqlite> insert into CURRENT_MED (TERM_TEXT) values ('TTTTT');
sqlite> insert into CURRENT_MED (TERM_TEXT) values ('aaaaaaa');
sqlite> insert into QR3PARAMS (ED) values (0);
sqlite> insert into QR3PARAMS (ED) values (0);
sqlite> insert into QR3PARAMS (ED) values (0);
sqlite> insert into QR3PARAMS (ED) values (1);
sqlite> insert into QR3PARAMS (ED) values (0);
sqlite> select * from QR3PARAMS;
1|0
2|0
3|0
4|1
5|0
sqlite> select * from CURRENT_MED;
1|Sildenafilame
2|Tadalafilemete
3|Vardenafilatala
4|TTTTT
5|aaaaaaa
sqlite>
sqlite> 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);
sqlite> SELECT changes();
5
sqlite> select * from QR3PARAMS;
1|1
2|1
3|1
4|1
5|

Null value again on ID 5 above.

> On Wed, Nov 13, 2019 at 5:01 PM Keith Medcalf, on
>
> >
> > Both queries update all rows in QR3PARAMS since there is no WHERE clause
> > to limit which rows are updated, so when you say "does less updates" what
> > do you mean, since it is manifestly impossible for one to do less updates
> > than the other -- both update every row or the table.
> >
> > Secondly, in the first update, the subquery is only executed if
> > QR3PARAMS.ED != 1 so therefore the clause "QR3PARAMS.ED = 1" in the where
> > clause of the subquery will always be false and is therefore meaningless
> > and serves only to consume CPU to no effect.
> >
> > The difference between them is that the second one will update the
> > QR3PARAMS to NULL if there are no CURRENT_MED rows with an ID =
> > QR3PARAMS.ID even if QR3PARAMS.ED = 1, while in the first one this will
> > not occur (QR3PARAMS.ED will be set to 1).
> >
> > --
> > The fact that there's a Highway to Hell but only a Stairway to Heaven says
> > a lot about anticipated traffic volume.
> >
> > >-----Original Message-----
> > >From: sqlite-users, on
> > >Behalf Of Bart Smissaert
> > >Sent: Wednesday, 13 November, 2019 04:41
> > >To: General Discussion of SQLite Database, on
> > >[hidden email]>
> > >Subject: [sqlite] Why do these 2 updates give different results?
> > >
> > >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.
> > >
> > >RBS
> > >_______________________________________________
> > >sqlite-users mailing list
> > >[hidden email]
> > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do these 2 updates give different results?

Jose Isaias Cabrera-4
In reply to this post by Donald Griggs

Donald Griggs, on Wednesday, November 13, 2019 12:26 PM, wrote...
>
> >
> > When dealing with ED and sildenafil, getting a NULL result disappoints
> > everyone.     ;-)
> >

Good one!  NOT that **I** know anything about that.  Hahahahah...
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do these 2 updates give different results?

David Raymond
In reply to this post by Bart Smissaert
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do these 2 updates give different results?

Bart Smissaert
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 <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do these 2 updates give different results?

Keith Medcalf

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do these 2 updates give different results?

Bart Smissaert
Actually, I still have 2 queries as before the one mentioned I have:

UPDATE QR3PARAMS SET ED =
(SELECT 1 FROM PROBLEMS WHERE
(READ_CODE GLOB 'Eu522*' OR READ_CODE GLOB 'E2273*' OR READ_CODE =
'1777409015')
AND ID = QR3PARAMS.ID LIMIT 1)

But I thought to keep matters simple (and maybe a bit faster) I would split
it in 2.

RBS

On Wed, Nov 13, 2019 at 8:39 PM Keith Medcalf <[hidden email]> wrote:

>
> On Wednesday, 13 November, 2019 13:26, Bart Smissaert <
> [hidden email]> 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 <[hidden email]>
> >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
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do these 2 updates give different results?

Peter da Silva-2
>
> This almost seems like a job for a view.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do these 2 updates give different results?

Bart Smissaert
This looks to work fine and runs fast as well, combining the 2 queries I
have now:

UPDATE QR3PARAMS SET ED = CASE WHEN
ID IN
(SELECT ID FROM PROBLEMS WHERE
READ_CODE GLOB 'Eu522*' OR READ_CODE GLOB 'E2273*' OR READ_CODE =
'1777409015') OR
ID IN
(SELECT ID FROM CURRENT_MED WHERE
TERM_TEXT GLOB 'Sildenafil*' OR TERM_TEXT GLOB 'Tadalafil*' OR TERM_TEXT
GLOB 'Vardenafil*')
THEN 1 ELSE 0 END

RBS

On Wed, Nov 13, 2019 at 9:09 PM Peter da Silva <[hidden email]> wrote:

> >
> > This almost seems like a job for a view.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users