Re: Question about: Adding a record to a table with select failure

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

Re: Question about: Adding a record to a table with select failure

Doug
WRT Jose's original context, and just for my enlightment, what happens with the following:

insert into t (a, b, c, d, e, idate)
SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999';

where p999 does not define a record? Is a new record inserted with values of a,b,c, and e null?

> -----Original Message-----
> From: sqlite-users <[hidden email]>
> On Behalf Of Jose Isaias Cabrera
> Sent: Friday, November 15, 2019 6:21 AM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] Adding a record to a table with one value
> change
>
>
> Simon Slavin, on Thursday, November 14, 2019 06:48 PM, wrote...
> >
> > On 14 Nov 2019, at 10:27pm, Jake Thaw, on
> >
> > > Why not like this?
> > >
> > > insert into t (a, b, c, d, e, idate)
> > > SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001'
> ORDER BY
> > > idate desc limit 1;
> >
> > Dammit.  I thought I had tried this, and received a syntax
> error.  Now I see that it was because I missed out a comma.
> Thanks for the correction.
>
> That just lets you know that you are a human. ;-)
>
> > Good illustration of why responses should go to the list rather
> than direct to the OP.
>
> Indeed.
> _______________________________________________
> 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: Question about: Adding a record to a table with select failure

Jose Isaias Cabrera-4

Doug, on Friday, November 15, 2019 11:42 AM, wrote...
>
> WRT Jose's original context, and just for my enlightment, what happens with the following:
>
> insert into t (a, b, c, d, e, idate)
> SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999';
>
> where p999 does not define a record? Is a new record inserted with values of a,b,c, and e null?

It does not get inserted.

sqlite> drop table t;
sqlite> create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
sqlite> insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, '2019-02-11');
sqlite> insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, '2019-02-11');
sqlite> insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, '2019-02-11');
sqlite> insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, '2019-02-11');
sqlite> insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, '2019-02-11');
sqlite> select * from t;
1|p001|1|2|n|4|2019-02-11
2|p002|2|2|n|4|2019-02-11
3|p003|3|2|n|4|2019-02-11
4|p004|4|2|y|4|2019-02-11
5|p005|5|2|y|4|2019-02-11
sqlite> insert into t (a, b, c, d, e, idate)
   ...> SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999';
sqlite> select * from t;
1|p001|1|2|n|4|2019-02-11
2|p002|2|2|n|4|2019-02-11
3|p003|3|2|n|4|2019-02-11
4|p004|4|2|y|4|2019-02-11
5|p005|5|2|y|4|2019-02-11
sqlite>

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: Question about: Adding a record to a table with select failure

Simon Slavin-3
On 15 Nov 2019, at 4:48pm, Jose Isaias Cabrera <[hidden email]> wrote:

> It does not get inserted.

The SELECT returns zero lines.  Therefore zero lines get inserted.  You might like to try one where the SELECT returns more than one line.
_______________________________________________
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: Question about: Adding a record to a table with select failure

Jose Isaias Cabrera-4

Simon Slavin, on Friday, November 15, 2019 11:58 AM, wrote...
>
> On 15 Nov 2019, at 4:48pm, Jose Isaias Cabrera, on
>
> > It does not get inserted.
>
> The SELECT returns zero lines.  Therefore zero lines get inserted.  You might like to try
> one where the SELECT returns more than one line.

Yes, I expected this.  I was just trying it as a show-result type of deal. :-)

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: Question about: Adding a record to a table with select failure

Jose Isaias Cabrera-4
In reply to this post by Doug

Doug, on Friday, November 15, 2019 11:42 AM, wrote...
>
> WRT Jose's original context, and just for my enlightment, what happens with the following:
>
> insert into t (a, b, c, d, e, idate)
> SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999';
>
> where p999 does not define a record? Is a new record inserted with values of a,b,c, and e null?

Ok, I promise that this will be the last email on this for me:

I just came to my senses, and sometimes, I need to insert when the 'a' value does not exists, as Doug just brought to my attention.  So, I am trying to insert a record with two new values using the last existing 'a'.  If a does not exists, then I need to add that record with the two values.  I have been trying a few INSERT with CASEs, but nothing is working.  I know one of you will make it look easy, but this is what I have done as of now:
create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, '2019-02-11');
select * from t;
1|p001|1|2|n|4|2019-02-11
2|p002|2|2|n|4|2019-02-11
3|p003|3|2|n|4|2019-02-11
4|p004|4|2|y|4|2019-02-11
5|p005|5|2|y|4|2019-02-11

I have tried various combination of the following,

insert into t (a, b, c, d, e, idate) VALUES
(
    CASE
       SELECT a from t WHERE a = 'p006' idate desc limit 1
    WHEN a = NULL
    THEN 'p006',1,2,'y',4,'2019-02-12'
    ELSE SELECT a, b, c, 'y', e, '2019-20-12' from t WHERE a = 'p006' idate desc limit 1
    END
);
Error: near "SELECT": syntax error
sqlite>

But, different syntax error have popped.  Any help would be greatly appreciated.  Thanks.

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: Question about: Adding a record to a table with select failure

Simon Slavin-3
On 15 Nov 2019, at 9:04pm, Jose Isaias Cabrera <[hidden email]> wrote:

>   CASE
>       SELECT a from t WHERE a = 'p006' idate desc limit 1
>    WHEN a = NULL
>    THEN 'p006',1,2,'y',4,'2019-02-12'
>    ELSE SELECT a, b, c, 'y', e, '2019-20-12' from t WHERE a = 'p006' idate desc limit 1
>    END

The thing after THEN must be an expression, not a list of expressions separated by commas.

You could probably use another SELECT after THEN to retrieve the list of values, just as you have after ELSE.

(Note I have not actually tried this to make sure it works.)
_______________________________________________
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: Question about: Adding a record to a table with select failure

Jake
In reply to this post by Jose Isaias Cabrera-4
One approach might be something like this:

INSERT INTO t (a, b, c, d, e, idate)
SELECT 'p006',
       Coalesce(b, 1),
       Coalesce(c, 2),
       'y',
       Coalesce(e, 4),
       '2019-20-12'
  FROM (SELECT 1)
  LEFT JOIN
       (SELECT a, b, c, e FROM t WHERE a = 'p006' ORDER BY idate DESC LIMIT 1);

A slightly more succinct (but not universal) way:
Note: see point 1 of
https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause

INSERT INTO t (a, b, c, d, e, idate)
SELECT IfNull('p006', Max(idate)),
       IfNull(b, 1),
       IfNull(c, 2),
       'y',
       IfNull(e, 4),
       '2019-20-12'
  FROM t
 WHERE a = 'p006';

On Sat, Nov 16, 2019 at 8:04 AM Jose Isaias Cabrera <[hidden email]> wrote:

>
>
> Doug, on Friday, November 15, 2019 11:42 AM, wrote...
> >
> > WRT Jose's original context, and just for my enlightment, what happens with the following:
> >
> > insert into t (a, b, c, d, e, idate)
> > SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999';
> >
> > where p999 does not define a record? Is a new record inserted with values of a,b,c, and e null?
>
> Ok, I promise that this will be the last email on this for me:
>
> I just came to my senses, and sometimes, I need to insert when the 'a' value does not exists, as Doug just brought to my attention.  So, I am trying to insert a record with two new values using the last existing 'a'.  If a does not exists, then I need to add that record with the two values.  I have been trying a few INSERT with CASEs, but nothing is working.  I know one of you will make it look easy, but this is what I have done as of now:
> create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
> insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, '2019-02-11');
> select * from t;
> 1|p001|1|2|n|4|2019-02-11
> 2|p002|2|2|n|4|2019-02-11
> 3|p003|3|2|n|4|2019-02-11
> 4|p004|4|2|y|4|2019-02-11
> 5|p005|5|2|y|4|2019-02-11
>
> I have tried various combination of the following,
>
> insert into t (a, b, c, d, e, idate) VALUES
> (
>     CASE
>        SELECT a from t WHERE a = 'p006' idate desc limit 1
>     WHEN a = NULL
>     THEN 'p006',1,2,'y',4,'2019-02-12'
>     ELSE SELECT a, b, c, 'y', e, '2019-20-12' from t WHERE a = 'p006' idate desc limit 1
>     END
> );
> Error: near "SELECT": syntax error
> sqlite>
>
> But, different syntax error have popped.  Any help would be greatly appreciated.  Thanks.
>
> josé
> _______________________________________________
> 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: Question about: Adding a record to a table with select failure

Jose Isaias Cabrera-4

Jake Thaw, on Saturday, November 16, 2019 08:39 AM, wrote...​

>  ​
> One approach might be something like this:​
> ​
> INSERT INTO t (a, b, c, d, e, idate)​
> SELECT 'p006',​
>        Coalesce(b, 1),​
>        Coalesce(c, 2),​
>        'y',​
>        Coalesce(e, 4),​
>        '2019-20-12'​
>   FROM (SELECT 1)​
>   LEFT JOIN​
>        (SELECT a, b, c, e FROM t WHERE a = 'p006' ORDER BY idate DESC LIMIT 1);​
> ​
> A slightly more succinct (but not universal) way:​
> Note: see point 1 of​
> https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause​
> ​
> INSERT INTO t (a, b, c, d, e, idate)​
> SELECT IfNull('p006', Max(idate)),​
>        IfNull(b, 1),​
>        IfNull(c, 2),​
>        'y',​
>        IfNull(e, 4),​
>        '2019-20-12'​
>   FROM t​
>  WHERE a = 'p006';​

Thanks, Jake.  I like this last one.  I appreciate it.  Thanks.​

josé​

> On Sat, Nov 16, 2019 at 8:04 AM Jose Isaias Cabrera, on ​
> >​
> >​
> > Doug, on Friday, November 15, 2019 11:42 AM, wrote...​
> > >​
> > > WRT Jose's original context, and just for my enlightment, what happens with the following:​
> > >​
> > > insert into t (a, b, c, d, e, idate)​
> > > SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999';​
> > >​
> > > where p999 does not define a record? Is a new record inserted with values of a,b,c, and e null?​
> >​
> > Ok, I promise that this will be the last email on this for me:​
> >​
> > I just came to my senses, and sometimes, I need to insert when the 'a' value does not exists, as Doug just brought to my attention.  So, I am trying to insert a record with two new values using the last existing 'a'.  If a does not exists, then I need to add that record with the two values.  I have been trying a few INSERT with CASEs, but nothing is working.  I know one of you will make it look easy, but this is what I have done as of now:​
> > create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);​
> > insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, '2019-02-11');​
> > insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, '2019-02-11');​
> > insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, '2019-02-11');​
> > insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, '2019-02-11');​
> > insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, '2019-02-11');​
> > select * from t;​
> > 1|p001|1|2|n|4|2019-02-11​
> > 2|p002|2|2|n|4|2019-02-11​
> > 3|p003|3|2|n|4|2019-02-11​
> > 4|p004|4|2|y|4|2019-02-11​
> > 5|p005|5|2|y|4|2019-02-11​
> >​
> > I have tried various combination of the following,​
> >​
> > insert into t (a, b, c, d, e, idate) VALUES​
> > (​
> >     CASE​
> >        SELECT a from t WHERE a = 'p006' idate desc limit 1​
> >     WHEN a = NULL​
> >     THEN 'p006',1,2,'y',4,'2019-02-12'​
> >     ELSE SELECT a, b, c, 'y', e, '2019-20-12' from t WHERE a = 'p006' idate desc limit 1​
> >     END​
> > );​
> > Error: near "SELECT": syntax error​
> > sqlite>​
> >​
> > But, different syntax error have popped.  Any help would be greatly appreciated.  Thanks.​
> >​
> > 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: [EXTERNAL] Re: Question about: Adding a record to a table with select failure

Hick Gunter
In reply to this post by Doug
Nothing. The select returns no rows so no rows are inserted.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Doug
Gesendet: Freitag, 15. November 2019 17:42
An: 'SQLite mailing list' <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Question about: Adding a record to a table with select failure

WRT Jose's original context, and just for my enlightment, what happens with the following:

insert into t (a, b, c, d, e, idate)
SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999';

where p999 does not define a record? Is a new record inserted with values of a,b,c, and e null?

> -----Original Message-----
> From: sqlite-users <[hidden email]>
> On Behalf Of Jose Isaias Cabrera
> Sent: Friday, November 15, 2019 6:21 AM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] Adding a record to a table with one value change
>
>
> Simon Slavin, on Thursday, November 14, 2019 06:48 PM, wrote...
> >
> > On 14 Nov 2019, at 10:27pm, Jake Thaw, on
> >
> > > Why not like this?
> > >
> > > insert into t (a, b, c, d, e, idate) SELECT a, b, c, 'y', e,
> > > '2019-02-12' FROM t WHERE a = 'p001'
> ORDER BY
> > > idate desc limit 1;
> >
> > Dammit.  I thought I had tried this, and received a syntax
> error.  Now I see that it was because I missed out a comma.
> Thanks for the correction.
>
> That just lets you know that you are a human. ;-)
>
> > Good illustration of why responses should go to the list rather
> than direct to the OP.
>
> Indeed.
> _______________________________________________
> 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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: Question about: Adding a record to a table with select failure

Doug
In reply to this post by Jose Isaias Cabrera-4
> -----Original Message-----
> From: sqlite-users <[hidden email]>
> On Behalf Of Jose Isaias Cabrera
> Sent: Saturday, November 16, 2019 10:43 AM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] Question about: Adding a record to a table
> with select failure
>
>
> Jake Thaw, on Saturday, November 16, 2019 08:39 AM, wrote...​
> >
> > One approach might be something like this:​
> >
> > INSERT INTO t (a, b, c, d, e, idate)​
> > SELECT 'p006',​
> >        Coalesce(b, 1),​
> >        Coalesce(c, 2),​
> >        'y',​
> >        Coalesce(e, 4),​
> >        '2019-20-12'​
> >   FROM (SELECT 1)​
> >   LEFT JOIN​
> >        (SELECT a, b, c, e FROM t WHERE a = 'p006' ORDER BY idate
> DESC LIMIT 1);​
> >
> > A slightly more succinct (but not universal) way:​
> > Note: see point 1 of​
> >
> https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_n
> on_aggregate_result_columns_that_are_not_in_the_group_by_clause​
> >
> > INSERT INTO t (a, b, c, d, e, idate)​
> > SELECT IfNull('p006', Max(idate)),​
> >        IfNull(b, 1),​
> >        IfNull(c, 2),​
> >        'y',​
> >        IfNull(e, 4),​
> >        '2019-20-12'​
> >   FROM t​
> >  WHERE a = 'p006';​

I think that you will never insert the first record with a query like this, since the select returns 0 records of there are none in the database yet.
Doug


_______________________________________________
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: Question about: Adding a record to a table with select failure

Jose Isaias Cabrera-4

Doug, on Monday, November 18, 2019 12:31 PM, wrote...
Jose Isaias Cabrera
[clip]

> > >
> > > INSERT INTO t (a, b, c, d, e, idate)​
> > > SELECT IfNull('p006', Max(idate)),​
> > >        IfNull(b, 1),​
> > >        IfNull(c, 2),​
> > >        'y',​
> > >        IfNull(e, 4),​
> > >        '2019-20-12'​
> > >   FROM t​
> > >  WHERE a = 'p006';​
>
> I think that you will never insert the first record with a query like this, since
> the select returns 0 records of there are none in the database yet.

Well, it does...
sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a, idate));
sqlite> INSERT INTO t
   ...> SELECT IfNull('p001', Max(idate)),
   ...>        IfNull(b, 1),
   ...>        IfNull(c, 2),
   ...>        IfNull(d,'n'),
   ...>        IfNull(e, 4),
   ...>        '2019-20-11'
   ...>  FROM t
   ...>  WHERE a = 'p001';
sqlite> select * from t;
p001|1|2|n|4|2019-20-11
sqlite>

And, since I put an uniqueness on a and idate, now these can not be repeated, so if I run the same command again,
sqlite> INSERT INTO t
   ...> SELECT IfNull('p001', Max(idate)),
   ...>        IfNull(b, 1),
   ...>        IfNull(c, 2),
   ...>        IfNull(d,'n'),
   ...>        IfNull(e, 4),
   ...>        '2019-02-11'
   ...>  FROM t
   ...>  WHERE a = 'p001';
Error: UNIQUE constraint failed: t.a, t.idate
sqlite>

I do not get a repeated record for 'p001' and 2019-02-11; But if they are different,
sqlite> INSERT INTO t
   ...> SELECT IfNull('p002', Max(idate)),
   ...>        IfNull(b, 1),
   ...>        IfNull(c, 2),
   ...>        IfNull(d,'n'),
   ...>        IfNull(e, 4),
   ...>        '2019-02-11'
   ...>  FROM t
   ...>  WHERE a = 'p002';
sqlite> select * from t;
p001|1|2|n|4|2019-02-11
p002|1|2|n|4|2019-02-11

It'll work. Thanks.

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: Question about: Adding a record to a table with select failure

Doug
I'm really confused now. I don't understand the semantics of:
SELECT IfNull('p006', Max(idate)),
       IfNull(b, 1),
       IfNull(c, 2),
       'y',
       IfNull(e, 4),
       '2019-20-12'
  FROM t
  WHERE a = 'p006';

versus this:
SELECT (a,b,c,d,e,idate) from t where a = "p006"

Doesn't the where clause that cannot be satisfied in both cases guarantee that no rows will be selected, when there are no records in the database?
Doug

> -----Original Message-----
> From: sqlite-users <[hidden email]>
> On Behalf Of Jose Isaias Cabrera
> Sent: Monday, November 18, 2019 12:11 PM
> To: 'SQLite mailing list' <[hidden email]>
> Subject: Re: [sqlite] Question about: Adding a record to a table
> with select failure
>
>
> Doug, on Monday, November 18, 2019 12:31 PM, wrote...
> Jose Isaias Cabrera
> [clip]
> > > >
> > > > INSERT INTO t (a, b, c, d, e, idate)​
> > > > SELECT IfNull('p006', Max(idate)),​
> > > >        IfNull(b, 1),​
> > > >        IfNull(c, 2),​
> > > >        'y',​
> > > >        IfNull(e, 4),​
> > > >        '2019-20-12'​
> > > >   FROM t​
> > > >  WHERE a = 'p006';​
> >
> > I think that you will never insert the first record with a query
> like this, since
> > the select returns 0 records of there are none in the database
> yet.
>
> Well, it does...
> sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a,
> idate));
> sqlite> INSERT INTO t
>    ...> SELECT IfNull('p001', Max(idate)),
>    ...>        IfNull(b, 1),
>    ...>        IfNull(c, 2),
>    ...>        IfNull(d,'n'),
>    ...>        IfNull(e, 4),
>    ...>        '2019-20-11'
>    ...>  FROM t
>    ...>  WHERE a = 'p001';
> sqlite> select * from t;
> p001|1|2|n|4|2019-20-11
> sqlite>
>
> And, since I put an uniqueness on a and idate, now these can not
> be repeated, so if I run the same command again,
> sqlite> INSERT INTO t
>    ...> SELECT IfNull('p001', Max(idate)),
>    ...>        IfNull(b, 1),
>    ...>        IfNull(c, 2),
>    ...>        IfNull(d,'n'),
>    ...>        IfNull(e, 4),
>    ...>        '2019-02-11'
>    ...>  FROM t
>    ...>  WHERE a = 'p001';
> Error: UNIQUE constraint failed: t.a, t.idate
> sqlite>
>
> I do not get a repeated record for 'p001' and 2019-02-11; But if
> they are different,
> sqlite> INSERT INTO t
>    ...> SELECT IfNull('p002', Max(idate)),
>    ...>        IfNull(b, 1),
>    ...>        IfNull(c, 2),
>    ...>        IfNull(d,'n'),
>    ...>        IfNull(e, 4),
>    ...>        '2019-02-11'
>    ...>  FROM t
>    ...>  WHERE a = 'p002';
> sqlite> select * from t;
> p001|1|2|n|4|2019-02-11
> p002|1|2|n|4|2019-02-11
>
> It'll work. Thanks.
>
> josé
>
>
> _______________________________________________
> 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: Question about: Adding a record to a table with select failure

Jose Isaias Cabrera-4

Doug, on Monday, November 18, 2019 02:48 PM, wrote...

>
> I'm really confused now. I don't understand the semantics of:
> SELECT IfNull('p006', Max(idate)),
>        IfNull(b, 1),
>        IfNull(c, 2),
>        'y',
>        IfNull(e, 4),
>        '2019-20-12'
>   FROM t
>   WHERE a = 'p006';
>
> versus this:
> SELECT (a,b,c,d,e,idate) from t where a = "p006"
>
> Doesn't the where clause that cannot be satisfied in both cases guarantee that no rows will
> be selected, when there are no records in the database?

Imagine this select:

SELECT 'p006',1, 2, 'y', 4, '2019-02-11';

The IfNull is bringing these values back if the 'p006' does not exists.  Try the above select in SQLite3 tool.  You will get a record.  Even without table.  Since the original question was how can I INSERT... this will check the table for 'p006', if it exists, it will bring some of the data from that existing record and insert a new one. Ihth.

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: Question about: Adding a record to a table with select failure

Keith Medcalf
In reply to this post by Doug

No.  This is an aggregate query that relies on the fact that SQLite3 will choose the values from (one of) the row(s) containing the aggregate to satisfy select scalars that are not aggregates.  Consider the query:

select a, max(idate), b from t where a == 'p006';

This will return the maximum value of idate for all the rows in t where a == 'p006', and the values of a and b from (the same) one of the rows on which this maximum is found.  If no rows matching the condition a == 'p006' then the maximum is NULL and the values of a and b are also NULL since there is no row from which the values may be taken.

So, this query will return the values b, c, e from the (one of the rows) with the max(idate) from all the rows in t having a == 'p006', or NULL for those values if no such row exists.  If no such row exists then the IfNull function will convert those NULL values into the given values.  The first ifnull will never actually be executed (since the first value is not null, the second, max(idate), will never be used).  However, since IfNull is a function, in this case taking two arguments, all the arguments must be evaluated BEFORE the function can be evaluated.

This relies on two implementation details particular to SQLite3 which hold at present, but may of course change at any time:
(1) that selecting a non-aggregate scalar column will return a value from (one of) the row(s) matching the value of the aggregate (most RDBMS used to do this, most now throw an error at this construct); and,
(2) that the optimizer will not optimize "IfNull('p006', max(idate))" into 'p006' since the result must always be 'p006' which would of course render the select to be a simple select and not an aggregate causing "all hell to break loose".

--
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 Doug
>Sent: Monday, 18 November, 2019 12:49
>To: 'SQLite mailing list' <[hidden email]>
>Subject: Re: [sqlite] Question about: Adding a record to a table with
>select failure
>
>I'm really confused now. I don't understand the semantics of:
>SELECT IfNull('p006', Max(idate)),
>       IfNull(b, 1),
>       IfNull(c, 2),
>       'y',
>       IfNull(e, 4),
>       '2019-20-12'
>  FROM t
>  WHERE a = 'p006';
>
>versus this:
>SELECT (a,b,c,d,e,idate) from t where a = "p006"
>
>Doesn't the where clause that cannot be satisfied in both cases guarantee
>that no rows will be selected, when there are no records in the database?
>Doug
>
>> -----Original Message-----
>> From: sqlite-users <[hidden email]>
>> On Behalf Of Jose Isaias Cabrera
>> Sent: Monday, November 18, 2019 12:11 PM
>> To: 'SQLite mailing list' <[hidden email]>
>> Subject: Re: [sqlite] Question about: Adding a record to a table
>> with select failure
>>
>>
>> Doug, on Monday, November 18, 2019 12:31 PM, wrote...
>> Jose Isaias Cabrera
>> [clip]
>> > > >
>> > > > INSERT INTO t (a, b, c, d, e, idate)​
>> > > > SELECT IfNull('p006', Max(idate)),​
>> > > >        IfNull(b, 1),​
>> > > >        IfNull(c, 2),​
>> > > >        'y',​
>> > > >        IfNull(e, 4),​
>> > > >        '2019-20-12'​
>> > > >   FROM t​
>> > > >  WHERE a = 'p006';​
>> >
>> > I think that you will never insert the first record with a query
>> like this, since
>> > the select returns 0 records of there are none in the database
>> yet.
>>
>> Well, it does...
>> sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a,
>> idate));
>> sqlite> INSERT INTO t
>>    ...> SELECT IfNull('p001', Max(idate)),
>>    ...>        IfNull(b, 1),
>>    ...>        IfNull(c, 2),
>>    ...>        IfNull(d,'n'),
>>    ...>        IfNull(e, 4),
>>    ...>        '2019-20-11'
>>    ...>  FROM t
>>    ...>  WHERE a = 'p001';
>> sqlite> select * from t;
>> p001|1|2|n|4|2019-20-11
>> sqlite>
>>
>> And, since I put an uniqueness on a and idate, now these can not
>> be repeated, so if I run the same command again,
>> sqlite> INSERT INTO t
>>    ...> SELECT IfNull('p001', Max(idate)),
>>    ...>        IfNull(b, 1),
>>    ...>        IfNull(c, 2),
>>    ...>        IfNull(d,'n'),
>>    ...>        IfNull(e, 4),
>>    ...>        '2019-02-11'
>>    ...>  FROM t
>>    ...>  WHERE a = 'p001';
>> Error: UNIQUE constraint failed: t.a, t.idate
>> sqlite>
>>
>> I do not get a repeated record for 'p001' and 2019-02-11; But if
>> they are different,
>> sqlite> INSERT INTO t
>>    ...> SELECT IfNull('p002', Max(idate)),
>>    ...>        IfNull(b, 1),
>>    ...>        IfNull(c, 2),
>>    ...>        IfNull(d,'n'),
>>    ...>        IfNull(e, 4),
>>    ...>        '2019-02-11'
>>    ...>  FROM t
>>    ...>  WHERE a = 'p002';
>> sqlite> select * from t;
>> p001|1|2|n|4|2019-02-11
>> p002|1|2|n|4|2019-02-11
>>
>> It'll work. Thanks.
>>
>> josé
>>
>>
>> _______________________________________________
>> 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: Question about: Adding a record to a table with select failure

Jose Isaias Cabrera-4

Keith Medcalf, on Monday, November 18, 2019 04:27 PM, wrote...
>
> This relies on two implementation details particular to SQLite3 which hold> at present,
> but may of course change at any time:
> (1) that selecting a non-aggregate scalar column will return a value from (one of) the
> row(s) matching the value of the aggregate (most RDBMS used to do this, most now throw
> an error at this construct); and,
> (2) that the optimizer will not optimize "IfNull('p006', max(idate))" into 'p006' since
> the result must always be 'p006' which would of course render the select to be a simple
> select and not an aggregate causing "all hell to break loose".

Thanks Keith.  So, you are saying that this is a bad INSERT, and I don't know much to argue, but is working. If I take out the first IfNull, and there is not, at least one instance of 'p006' in the table, the INSERT never works. I was thinking of using COALESCE, but that would also mean that one or the other would have to be not null. Any suggestion would be appreciated.

[clip]

> >SELECT IfNull('p006', Max(idate)),
> >       IfNull(b, 1),
> >       IfNull(c, 2),
> >       'y',
> >       IfNull(e, 4),
> >       '2019-20-12'
> >  FROM t
> >  WHERE a = 'p006';
> >
> >versus this:
> >SELECT (a,b,c,d,e,idate) from t where a = "p006"
> >
> >Doesn't the where clause that cannot be satisfied in both cases guarantee
> >that no rows will be selected, when there are no records in the database?
> >Doug
> >
> >> -----Original Message-----
> >> From: sqlite-users, on

Jose Isaias Cabrera

> >> Sent: Monday, November 18, 2019 12:11 PM
> >
> >> Subject: Re: [sqlite] Question about: Adding a record to a table
> >> with select failure
> >>
> >>
> >> Doug, on Monday, November 18, 2019 12:31 PM, wrote...
> >> Jose Isaias Cabrera
> >> [clip]
> >> > > >
> >> > > > INSERT INTO t (a, b, c, d, e, idate)​
> >> > > > SELECT IfNull('p006', Max(idate)),​
> >> > > >        IfNull(b, 1),​
> >> > > >        IfNull(c, 2),​
> >> > > >        'y',​
> >> > > >        IfNull(e, 4),​
> >> > > >        '2019-20-12'​
> >> > > >   FROM t​
> >> > > >  WHERE a = 'p006';​
> >> >
> >> > I think that you will never insert the first record with a query
> >> like this, since
> >> > the select returns 0 records of there are none in the database
> >> yet.
> >>
> >> Well, it does...
> >> sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a,
> >> idate));
> >> sqlite> INSERT INTO t
> >>    ...> SELECT IfNull('p001', Max(idate)),
> >>    ...>        IfNull(b, 1),
> >>    ...>        IfNull(c, 2),
> >>    ...>        IfNull(d,'n'),
> >>    ...>        IfNull(e, 4),
> >>    ...>        '2019-20-11'
> >>    ...>  FROM t
> >>    ...>  WHERE a = 'p001';
> >> sqlite> select * from t;
> >> p001|1|2|n|4|2019-20-11
> >> sqlite>
> >>
> >> And, since I put an uniqueness on a and idate, now these can not
> >> be repeated, so if I run the same command again,
> >> sqlite> INSERT INTO t
> >>    ...> SELECT IfNull('p001', Max(idate)),
> >>    ...>        IfNull(b, 1),
> >>    ...>        IfNull(c, 2),
> >>    ...>        IfNull(d,'n'),
> >>    ...>        IfNull(e, 4),
> >>    ...>        '2019-02-11'
> >>    ...>  FROM t
> >>    ...>  WHERE a = 'p001';
> >> Error: UNIQUE constraint failed: t.a, t.idate
> >> sqlite>
> >>
> >> I do not get a repeated record for 'p001' and 2019-02-11; But if
> >> they are different,
> >> sqlite> INSERT INTO t
> >>    ...> SELECT IfNull('p002', Max(idate)),
> >>    ...>        IfNull(b, 1),
> >>    ...>        IfNull(c, 2),
> >>    ...>        IfNull(d,'n'),
> >>    ...>        IfNull(e, 4),
> >>    ...>        '2019-02-11'
> >>    ...>  FROM t
> >>    ...>  WHERE a = 'p002';
> >> sqlite> select * from t;
> >> p001|1|2|n|4|2019-02-11
> >> p002|1|2|n|4|2019-02-11
> >>
> >> It'll work. Thanks.
> >>
> >> 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: Question about: Adding a record to a table with select failure

Simon Slavin-3
On 18 Nov 2019, at 10:00pm, Jose Isaias Cabrera <[hidden email]> wrote:

> Thanks Keith.  So, you are saying that this is a bad INSERT, and I don't know much to argue, but is working. If I take out the first IfNull, and there is not, at least one instance of 'p006' in the table, the INSERT never works. I was thinking of using COALESCE, but that would also mean that one or the other would have to be not null. Any suggestion would be appreciated.

Being completely serious, whenever I see "undocumented" or "implementation dependent" or "optimization side-effect", or a SQL statement I can't parse in my head, I usually decide to do it in my programming language instead.  This simplifies testing and debugging, and makes things easier for the poor engineer who has to understand my code.

You can do clever things in a language like SQL which allows recursive construction clauses.  But what strikes me as ingenious when I'm writing it can look bizarre and impenetrable to me, or someone else, a year later.
_______________________________________________
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: Question about: Adding a record to a table with select failure

Jim Morris
Not sure this helps, a way to a conditionally insert based on if record
already exists, is a select with literals left outer joined to the maybe
record and use a where test value is null.

Something like this pseudo SQL

insert into T  (valueA, valueB') (select 'ValueA', 'ValueB' left outer
join T where T.valueA ='valueA' and T.valueA is null);


On 11/18/2019 2:14 PM, Simon Slavin wrote:

> On 18 Nov 2019, at 10:00pm, Jose Isaias Cabrera <[hidden email]> wrote:
>
>> Thanks Keith.  So, you are saying that this is a bad INSERT, and I don't know much to argue, but is working. If I take out the first IfNull, and there is not, at least one instance of 'p006' in the table, the INSERT never works. I was thinking of using COALESCE, but that would also mean that one or the other would have to be not null. Any suggestion would be appreciated.
> Being completely serious, whenever I see "undocumented" or "implementation dependent" or "optimization side-effect", or a SQL statement I can't parse in my head, I usually decide to do it in my programming language instead.  This simplifies testing and debugging, and makes things easier for the poor engineer who has to understand my code.
>
> You can do clever things in a language like SQL which allows recursive construction clauses.  But what strikes me as ingenious when I'm writing it can look bizarre and impenetrable to me, or someone else, a year later.
> _______________________________________________
> 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: Question about: Adding a record to a table with select failure

Keith Medcalf
In reply to this post by Jose Isaias Cabrera-4

On Monday, 18 November, 2019 15:01, Jose Isaias Cabrera <[hidden email]> wrote:

>Keith Medcalf, on Monday, November 18, 2019 04:27 PM, wrote...
>>
>> This relies on two implementation details particular to SQLite3 which
>> hold at present, but may of course change at any time:
>> (1) that selecting a non-aggregate scalar column will return a value
>> from (one of) the row(s) matching the value of the aggregate (most
>> RDBMS used to do this, most now throw an error at this construct); and,
>> (2) that the optimizer will not optimize "IfNull('p006', max(idate))"
>> into 'p006' since the result must always be 'p006' which would of
>> course render the select to be a simple select and not an aggregate
>> causing "all hell to break loose".

>Thanks Keith.  So, you are saying that this is a bad INSERT, and I don't
>know much to argue, but is working. If I take out the first IfNull, and
>there is not, at least one instance of 'p006' in the table, the INSERT
>never works. I was thinking of using COALESCE, but that would also mean
>that one or the other would have to be not null. Any suggestion would be
>appreciated.

No, what I am saying is that this particular insert is working with the current version of SQLite3 and is unlikely to work with any other RDBMS that uses SQL as this is using an "implementation detail" specific to SQLite3 that does not exist elsewhere; and, secondly that the particular construction used putting the aggregate function in the second argument of a coalesce where the first argument is a constant is dependent on the query optimizer not "optimizing way" the entire expression based on the fact that the first argument is a not-null constant (though if this optimized away the aggregate-ness of the query this would be a bug in the optimizer, since the optimization process should not cause different results to occur).

What I am saying is that you need to be aware of this and if you change versions of SQLite3 then you need to make sure that this query still operates as you intend it to operate.

So far as I know the first constraint (the scalar use of columns that are not part of the group by clause in a query) is unlikely to change because this is a significant change to backwards compatibility and Richard is unlikely to make such a change without warnings in second coming type.

The second is unlikely with SQLite3 because the optimizer is not based on a query re-write which would be more prone to this sort of issue.

The second issue can be bypassed entirely by using bound parameters (since there is no way except by examining the bound parameter at execution time to know that a bound parameter is not NULL) thus precluding the possibility of optimizing away the aggregate function.  As in:

select ifnull(:a, max(idate)),
       ifnull(b, 1),
       ifnull(c, 2),
       :y,
       ifnull(e, 4),
       :idate
  from t
 where a == :a

and binding values for :a, :y and :idate.  There is no way to know at prepare time that parameter :a cannot be null so therefore the ifnull(:a... and the evaluation of the aggregate cannot be optimized away.

>[clip]
>
>> >SELECT IfNull('p006', Max(idate)),
>> >       IfNull(b, 1),
>> >       IfNull(c, 2),
>> >       'y',
>> >       IfNull(e, 4),
>> >       '2019-20-12'
>> >  FROM t
>> >  WHERE a = 'p006';
>> >

--
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
Reply | Threaded
Open this post in threaded view
|

Re: Question about: Adding a record to a table with select failure

Peter da Silva-2
In reply to this post by Jim Morris
Assuming I'm understanding what the original message was about.

Isn't this what BEGIN; INSERT OR IGNORE; UPDATE; COMMIT is the right tool for?
_______________________________________________
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: [EXTERNAL] Re: Question about: Adding a record to a table with select failure

Hick Gunter
In reply to this post by Jose Isaias Cabrera-4
The Magic Max at work, forcing the query to return at least one record.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Jose Isaias Cabrera
Gesendet: Montag, 18. November 2019 20:11
An: 'SQLite mailing list' <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Question about: Adding a record to a table with select failure


Doug, on Monday, November 18, 2019 12:31 PM, wrote...
Jose Isaias Cabrera
[clip]

> > >
> > > INSERT INTO t (a, b, c, d, e, idate)​ SELECT IfNull('p006',
> > > Max(idate)),​
> > >        IfNull(b, 1),​
> > >        IfNull(c, 2),​
> > >        'y',​
> > >        IfNull(e, 4),​
> > >        '2019-20-12'​
> > >   FROM t​
> > >  WHERE a = 'p006';​
>
> I think that you will never insert the first record with a query like
> this, since the select returns 0 records of there are none in the database yet.

Well, it does...
sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a, idate));
sqlite> INSERT INTO t
   ...> SELECT IfNull('p001', Max(idate)),
   ...>        IfNull(b, 1),
   ...>        IfNull(c, 2),
   ...>        IfNull(d,'n'),
   ...>        IfNull(e, 4),
   ...>        '2019-20-11'
   ...>  FROM t
   ...>  WHERE a = 'p001';
sqlite> select * from t;
p001|1|2|n|4|2019-20-11
sqlite>

And, since I put an uniqueness on a and idate, now these can not be repeated, so if I run the same command again,
sqlite> INSERT INTO t
   ...> SELECT IfNull('p001', Max(idate)),
   ...>        IfNull(b, 1),
   ...>        IfNull(c, 2),
   ...>        IfNull(d,'n'),
   ...>        IfNull(e, 4),
   ...>        '2019-02-11'
   ...>  FROM t
   ...>  WHERE a = 'p001';
Error: UNIQUE constraint failed: t.a, t.idate
sqlite>

I do not get a repeated record for 'p001' and 2019-02-11; But if they are different,
sqlite> INSERT INTO t
   ...> SELECT IfNull('p002', Max(idate)),
   ...>        IfNull(b, 1),
   ...>        IfNull(c, 2),
   ...>        IfNull(d,'n'),
   ...>        IfNull(e, 4),
   ...>        '2019-02-11'
   ...>  FROM t
   ...>  WHERE a = 'p002';
sqlite> select * from t;
p001|1|2|n|4|2019-02-11
p002|1|2|n|4|2019-02-11

It'll work. Thanks.

josé


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12