IfNull works beautifully to allow
for this. Why it works with Max(idate) on the first select, I don't know, but,
if I take it out, it does not. Thanks.
> Doug
> > -Original Message-----
> > From: sqlite-users, on
Jose Isaias Cabrera
> > Sent: Monday, November 18,
alf Of Jose Isaias Cabrera
> Sent: Monday, November 18, 2019 12:11 PM
> To: 'SQLite mailing list'
> 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 Ca
Peter da Silva, on Monday, November 18, 2019 08:07 PM, wrote...
>
> 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?
The original message was about adding a new record using old values from an
exist
Thanks, Keith.
Keith Medcalf, on Monday, November 18, 2019 07:25 PM, wrote...
>
>
> On Monday, 18 November, 2019 15:01, Jose Isaias Cabrera, on
>
> >Keith Medcalf, on Monday, November 18, 2019 04:27 PM, wrote...
> >>
> >> This relies on two implementation details particular to SQLite3 which
> >>
Simon Slavin, on Monday, November 18, 2019 05:14 PM, wrote...
> 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.
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailm
On Monday, 18 November, 2019 15:01, Jose Isaias Cabrera
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-aggreg
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
On 18 Nov 2019, at 10:00pm, Jose Isaias Cabrera 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
>
t;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-
> >
ed 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 Behalf Of Jose Isaias Cabrera
>> Sent: Monday, November 18, 2019 12:11 PM
>> To: '
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';
>
>
rera
> Sent: Monday, November 18, 2019 12:11 PM
> To: 'SQLite mailing list'
> 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 Ca
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-
> -Original Message-
> From: sqlite-users
> On Behalf Of Jose Isaias Cabrera
> Sent: Saturday, November 16, 2019 10:43 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Question about: Adding a record to a table
> with select failure
>
>
> Jake Thaw,
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'
> FRO
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 LI
On 15 Nov 2019, at 9:04pm, Jose Isaias Cabrera 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 af
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?
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.
On 15 Nov 2019, at 4:48pm, Jose Isaias Cabrera 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
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?
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?
> -O
23 matches
Mail list logo
