Doug, on Tuesday, November 19, 2019 10:47 AM, wrote...
>
> Jose, at least two things bothers me about part of your query:
> IfNull('p006', Max(idate))
> The first is that 'p006' is never null so the second part of the ifnull()
> will never be used.
True.
> The second thing is that the
Jose, at least two things bothers me about part of your query:
IfNull('p006', Max(idate))
The first is that 'p006' is never null so the second part of the ifnull() will
never be used.
The second thing is that the result of this ifnull() is to set the value of the
"a" field. However, the
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
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
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
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
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
>
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
>
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
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';
>
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
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),
> > >
> -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, on Saturday, November 16, 2019
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'
>
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
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
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?
>
23 matches
Mail list logo
