Re: [sqlite] Checking differences in tables
Remove me On Wed, Feb 13, 2019, 12:54 PM Jose Isaias Cabrera > James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote... > >On Tue, 12 Feb 2019 15:05:29 + > >Jose Isaias Cabrera wrote: > > > >> >SELECT * From t WHERE datestamp = "20190208" > >> > >> Ok, Simon, I'll bite; :-) Imagine this table: > >> > >> t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate) > > > >That's better. > > > >> how would I find the differences in the fields based on the different > >> idate? > > > >select ... > >from t as now join t as then > >on now.idate = '20190208' > >and then.idate = '20190207' -- note single quotes > >and ... -- other things that match > >where ... --- things that don't match > > > >Can't be more specific than that when the question is "find the > >differences". > > This is the last fix on this, > > select new.a,old.b,new.b, 'difference in column b' as info from t as new > LEFT JOIN t as old ON > new.idate = '2019-02-12' > AND old.idate = '2019-02-11' > AND new.a = old.a > WHERE > new.b != old.b > UNION ALL > select new.a,old.c,new.c, 'difference in column c' as info from t as new > LEFT JOIN t as old ON > new.idate = '2019-02-12' > AND old.idate = '2019-02-11' > AND new.a = old.a > WHERE > new.c != old.c > UNION ALL > select new.d,old.d,new.d, 'difference in column d' as info from t as new > LEFT JOIN t as old ON > new.idate = '2019-02-12' > AND old.idate = '2019-02-11' > AND new.a = old.a > WHERE > new.d != old.d > UNION ALL > select new.a,old.e,new.e, 'difference in column e' as info from t as new > LEFT JOIN t as old ON > new.idate = '2019-02-12' > AND old.idate = '2019-02-11' > AND new.a = old.a > WHERE > new.e != old.e > ORDER by new.a; > > Result, > > p001|2|4|difference in column c > p001|4|3|difference in column e > p002|2|4|difference in column c > p003|2|4|difference in column c > p004|2|4|difference in column c > p005|5|3|difference in column b > p005|2|3|difference in column c > > This is the best way of doing this, correct? Thanks. > > josé > > PS: I've learn a few things in this last 7 days or so. Thanks so much > you Gurus. > > > ___ > 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
Re: [sqlite] Checking differences in tables
James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote... >On Tue, 12 Feb 2019 15:05:29 + >Jose Isaias Cabrera wrote: > >> >SELECT * From t WHERE datestamp = "20190208" >> >> Ok, Simon, I'll bite; :-) Imagine this table: >> >> t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate) > >That's better. > >> how would I find the differences in the fields based on the different >> idate? > >select ... >from t as now join t as then >on now.idate = '20190208' >and then.idate = '20190207' -- note single quotes >and ... -- other things that match >where ... --- things that don't match > >Can't be more specific than that when the question is "find the >differences". This is the last fix on this, select new.a,old.b,new.b, 'difference in column b' as info from t as new LEFT JOIN t as old ON new.idate = '2019-02-12' AND old.idate = '2019-02-11' AND new.a = old.a WHERE new.b != old.b UNION ALL select new.a,old.c,new.c, 'difference in column c' as info from t as new LEFT JOIN t as old ON new.idate = '2019-02-12' AND old.idate = '2019-02-11' AND new.a = old.a WHERE new.c != old.c UNION ALL select new.d,old.d,new.d, 'difference in column d' as info from t as new LEFT JOIN t as old ON new.idate = '2019-02-12' AND old.idate = '2019-02-11' AND new.a = old.a WHERE new.d != old.d UNION ALL select new.a,old.e,new.e, 'difference in column e' as info from t as new LEFT JOIN t as old ON new.idate = '2019-02-12' AND old.idate = '2019-02-11' AND new.a = old.a WHERE new.e != old.e ORDER by new.a; Result, p001|2|4|difference in column c p001|4|3|difference in column e p002|2|4|difference in column c p003|2|4|difference in column c p004|2|4|difference in column c p005|5|3|difference in column b p005|2|3|difference in column c This is the best way of doing this, correct? Thanks. josé PS: I've learn a few things in this last 7 days or so. Thanks so much you Gurus. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
Man, you guys are so smart... Thanks, Keith.
From: sqlite-users on behalf of
Keith Medcalf
Sent: Wednesday, February 13, 2019 02:31 AM
To: SQLite mailing list
Subject: Re: [sqlite] Checking differences in tables
insert into t (a, b, c, d, e, idate)
values ('p001', 1, 2,
(SELECT d FROM t
WHERE a = 'p001'
ORDER BY idate desc
limit 1
),
4, '2019-02-12');
---
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 [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Kees Nuyt
>Sent: Tuesday, 12 February, 2019 17:55
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Checking differences in tables
>
>On Tue, 12 Feb 2019 21:03:47 +, you wrote:
>
>>
>>
>>
>> David Raymond, on Tuesday, February 12, 2019 03:48 PM, wrote...
>>> Not sure if this will fix your specific issue, but if you're using
>a query as a single
>>> value it needs to be in parenthesis, so something like
>>>
>>> insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, (SELECT
>d FROM t WHERE a = 'p006'
>>> AND max(idate)), 4, '2019-02-12');
>>
>> I get,
>> sqlite> insert into t (a, b, c, d, e, idate)
>> values ('p001', 1, 2,
>> (SELECT d FROM t WHERE a = 'p001' AND max(idate)),
>> 4, '2019-02-12');
>> Error: misuse of aggregate function max()
>
>Try:
>insert into t (a, b, c, d, e, idate)
> values ('p001', 1, 2,
> (SELECT d FROM t
>WHERE a = 'p001'
> AND idate = (SELECT max(idate) FROM t WHERE a = 'p001')
> ),
> 4, '2019-02-12');
>
>--
>Regards,
>Kees Nuyt
>
>
>___
>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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
insert into t (a, b, c, d, e, idate)
values ('p001', 1, 2,
(SELECT d FROM t
WHERE a = 'p001'
ORDER BY idate desc
limit 1
),
4, '2019-02-12');
---
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 [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Kees Nuyt
>Sent: Tuesday, 12 February, 2019 17:55
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Checking differences in tables
>
>On Tue, 12 Feb 2019 21:03:47 +, you wrote:
>
>>
>>
>>
>> David Raymond, on Tuesday, February 12, 2019 03:48 PM, wrote...
>>> Not sure if this will fix your specific issue, but if you're using
>a query as a single
>>> value it needs to be in parenthesis, so something like
>>>
>>> insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, (SELECT
>d FROM t WHERE a = 'p006'
>>> AND max(idate)), 4, '2019-02-12');
>>
>> I get,
>> sqlite> insert into t (a, b, c, d, e, idate)
>> values ('p001', 1, 2,
>> (SELECT d FROM t WHERE a = 'p001' AND max(idate)),
>> 4, '2019-02-12');
>> Error: misuse of aggregate function max()
>
>Try:
>insert into t (a, b, c, d, e, idate)
> values ('p001', 1, 2,
> (SELECT d FROM t
>WHERE a = 'p001'
> AND idate = (SELECT max(idate) FROM t WHERE a = 'p001')
> ),
> 4, '2019-02-12');
>
>--
>Regards,
>Kees Nuyt
>
>
>___
>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
Re: [sqlite] Checking differences in tables
Kees Nuyt, on Tuesday, February 12, 2019 07:55 PM, wrote...
>On Tue, 12 Feb 2019 21:03:47 +, you wrote:
>> David Raymond, on Tuesday, February 12, 2019 03:48 PM, wrote...
>>> Not sure if this will fix your specific issue, but if you're using a query
>>> as a single
>>> value it needs to be in parenthesis, so something like
>>>
>>> insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, (SELECT d FROM t
>>> WHERE a = 'p006'
>>> AND max(idate)), 4, '2019-02-12');
>>
>> I get,
>> sqlite> insert into t (a, b, c, d, e, idate)
>> values ('p001', 1, 2,
>> (SELECT d FROM t WHERE a = 'p001' AND max(idate)),
>> 4, '2019-02-12');
>> Error: misuse of aggregate function max()
>
>Try:
>insert into t (a, b, c, d, e, idate)
> values ('p001', 1, 2,
> (SELECT d FROM t
>WHERE a = 'p001'
> AND idate = (SELECT max(idate) FROM t WHERE a = 'p001')
> ),
> 4, '2019-02-12');
Man, you guys are good. Thanks. Yep, that works.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
On Tue, 12 Feb 2019 21:03:47 +, you wrote:
>
>
>
> David Raymond, on Tuesday, February 12, 2019 03:48 PM, wrote...
>> Not sure if this will fix your specific issue, but if you're using a query
>> as a single
>> value it needs to be in parenthesis, so something like
>>
>> insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, (SELECT d FROM t
>> WHERE a = 'p006'
>> AND max(idate)), 4, '2019-02-12');
>
> I get,
> sqlite> insert into t (a, b, c, d, e, idate)
> values ('p001', 1, 2,
> (SELECT d FROM t WHERE a = 'p001' AND max(idate)),
> 4, '2019-02-12');
> Error: misuse of aggregate function max()
Try:
insert into t (a, b, c, d, e, idate)
values ('p001', 1, 2,
(SELECT d FROM t
WHERE a = 'p001'
AND idate = (SELECT max(idate) FROM t WHERE a = 'p001')
),
4, '2019-02-12');
--
Regards,
Kees Nuyt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
David Raymond, on Tuesday, February 12, 2019 03:48 PM, wrote...
>Not sure if this will fix your specific issue, but if you're using a query as
>a single
>value it needs to be in parenthesis, so something like
>
> insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, (SELECT d FROM t
> WHERE a = 'p006'
>AND max(idate)), 4, '2019-02-12');
I get,
sqlite> insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, (SELECT d
FROM t WHERE a = 'p001' AND max(idate)), 4, '2019-02-12');
Error: misuse of aggregate function max()
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Jose Isaias Cabrera
Sent: Tuesday, February 12, 2019 3:42 PM
To: James K. Lowden; SQLite mailing list
Subject: Re: [sqlite] Checking differences in tables
>James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote...
>>On Tue, 12 Feb 2019 15:05:29 +
>>Jose Isaias Cabrera wrote:
>>
>>> >SELECT * From t WHERE datestamp = "20190208"
>>>
>>> Ok, Simon, I'll bite; :-) Imagine this table:
>>>
>>> t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate)
>>
>>That's better.
>>
>>> how would I find the differences in the fields based on the different
>>> idate?
>>
>>select ...
>>from t as now join t as then
>>on now.idate = '20190208'
>>and then.idate = '20190207' -- note single quotes
>>and ... -- other things that match
>>where ... --- things that don't match
>>
>>Can't be more specific than that when the question is "find the
>>differences".
>
>No, this is great. This is great, thanks.
One last question on this... Imagine this scenario...
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');
if I am inserting new records into the table, and there is a field, say "d", I
want to keep the last value of that field for the last inserted record, how can
I do this? I am trying this,
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, SELECT d FROM t
WHERE a = 'p001' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p002', 1, 2, SELECT d FROM t
WHERE a = 'p002' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p003', 1, 2, SELECT d FROM t
WHERE a = 'p003' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p004', 1, 2, SELECT d FROM t
WHERE a = 'p004' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p005', 1, 2, SELECT d FROM t
WHERE a = 'p005' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, SELECT d FROM t
WHERE a = 'p006' AND max(idate), 4, '2019-02-12'); -- new
But, of course, it's not working. I get,
Error: near "SELECT": syntax error
Thougths? thanks.
___
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
Not sure if this will fix your specific issue, but if you're using a query as a
single value it needs to be in parenthesis, so something like
insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, (SELECT d FROM t
WHERE a = 'p006' AND max(idate)), 4, '2019-02-12');
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Jose Isaias Cabrera
Sent: Tuesday, February 12, 2019 3:42 PM
To: James K. Lowden; SQLite mailing list
Subject: Re: [sqlite] Checking differences in tables
>James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote...
>>On Tue, 12 Feb 2019 15:05:29 +
>>Jose Isaias Cabrera wrote:
>>
>>> >SELECT * From t WHERE datestamp = "20190208"
>>>
>>> Ok, Simon, I'll bite; :-) Imagine this table:
>>>
>>> t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate)
>>
>>That's better.
>>
>>> how would I find the differences in the fields based on the different
>>> idate?
>>
>>select ...
>>from t as now join t as then
>>on now.idate = '20190208'
>>and then.idate = '20190207' -- note single quotes
>>and ... -- other things that match
>>where ... --- things that don't match
>>
>>Can't be more specific than that when the question is "find the
>>differences".
>
>No, this is great. This is great, thanks.
One last question on this... Imagine this scenario...
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');
if I am inserting new records into the table, and there is a field, say "d", I
want to keep the last value of that field for the last inserted record, how can
I do this? I am trying this,
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, SELECT d FROM t
WHERE a = 'p001' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p002', 1, 2, SELECT d FROM t
WHERE a = 'p002' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p003', 1, 2, SELECT d FROM t
WHERE a = 'p003' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p004', 1, 2, SELECT d FROM t
WHERE a = 'p004' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p005', 1, 2, SELECT d FROM t
WHERE a = 'p005' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, SELECT d FROM t
WHERE a = 'p006' AND max(idate), 4, '2019-02-12'); -- new
But, of course, it's not working. I get,
Error: near "SELECT": syntax error
Thougths? thanks.
___
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
Re: [sqlite] Checking differences in tables
>James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote...
>>On Tue, 12 Feb 2019 15:05:29 +
>>Jose Isaias Cabrera wrote:
>>
>>> >SELECT * From t WHERE datestamp = "20190208"
>>>
>>> Ok, Simon, I'll bite; :-) Imagine this table:
>>>
>>> t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate)
>>
>>That's better.
>>
>>> how would I find the differences in the fields based on the different
>>> idate?
>>
>>select ...
>>from t as now join t as then
>>on now.idate = '20190208'
>>and then.idate = '20190207' -- note single quotes
>>and ... -- other things that match
>>where ... --- things that don't match
>>
>>Can't be more specific than that when the question is "find the
>>differences".
>
>No, this is great. This is great, thanks.
One last question on this... Imagine this scenario...
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');
if I am inserting new records into the table, and there is a field, say "d", I
want to keep the last value of that field for the last inserted record, how can
I do this? I am trying this,
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, SELECT d FROM t
WHERE a = 'p001' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p002', 1, 2, SELECT d FROM t
WHERE a = 'p002' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p003', 1, 2, SELECT d FROM t
WHERE a = 'p003' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p004', 1, 2, SELECT d FROM t
WHERE a = 'p004' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p005', 1, 2, SELECT d FROM t
WHERE a = 'p005' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, SELECT d FROM t
WHERE a = 'p006' AND max(idate), 4, '2019-02-12'); -- new
But, of course, it's not working. I get,
Error: near "SELECT": syntax error
Thougths? thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote... >On Tue, 12 Feb 2019 15:05:29 + >Jose Isaias Cabrera wrote: > >> >SELECT * From t WHERE datestamp = "20190208" >> >> Ok, Simon, I'll bite; :-) Imagine this table: >> >> t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate) > >That's better. > >> how would I find the differences in the fields based on the different >> idate? > >select ... >from t as now join t as then >on now.idate = '20190208' >and then.idate = '20190207' -- note single quotes >and ... -- other things that match >where ... --- things that don't match > >Can't be more specific than that when the question is "find the >differences". No, this is great. This is great, thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
On Tue, 12 Feb 2019 15:05:29 + Jose Isaias Cabrera wrote: > >SELECT * From t WHERE datestamp = "20190208" > > Ok, Simon, I'll bite; :-) Imagine this table: > > t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate) That's better. > how would I find the differences in the fields based on the different > idate? select ... from t as now join t as then on now.idate = '20190208' and then.idate = '20190207' -- note single quotes and ... -- other things that match where ... --- things that don't match Can't be more specific than that when the question is "find the differences". --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
Simon Slavin, on Saturday, February 9, 2019 11:02 AM, wrote... >On 9 Feb 2019, at 3:49pm, Jose Isaias Cabrera wrote: > >> Imagine this scenario:I have this table, >> >> t (a PRIMARY KEY, b, c, d, e) >> >> that contains yesterday's data. Today, I rename that table to, >> >> t_20190208 (a PRIMARY KEY, b, c, d, e). > >That's not how you use tables. Your software shouldn't be automatically > creating or dropping tables during the life of your program. > >Have one table for your data for all days. >Add an extra column to the table for the datestamp. >Then to select all data for a particular day you do > >SELECT * From t WHERE datestamp = "20190208" Ok, Simon, I'll bite; :-) Imagine this table: t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate) how would I find the differences in the fields based on the different idate? a will never change because it is the only unique unchangeable input from date to date. But, everything else can change from date to date. Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
Warren Young, on Saturday, February 9, 2019 06:15 PM, wrote... >On Feb 9, 2019, at 12:20 PM, Jose Isaias Cabrera wrote: >> >> Warren Young, on Saturday, February 9, 2019 01:36 PM, wrote... >> > >> >You may be interested in the sqldiff program that comes with SQLite: >> >> Yeah, I know about it, but I thought it was for two different DBs > >It is, but that doesn’t affect whether you will be *interested* in it, the >word I chose very carefully. > >Ponder this: How does sqldiff work internally? > >Interested now? :) Ok, you got it. :-) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
On Feb 9, 2019, at 12:20 PM, Jose Isaias Cabrera wrote: > > Warren Young, on Saturday, February 9, 2019 01:36 PM, wrote... > > > >You may be interested in the sqldiff program that comes with SQLite: > > Yeah, I know about it, but I thought it was for two different DBs It is, but that doesn’t affect whether you will be *interested* in it, the word I chose very carefully. Ponder this: How does sqldiff work internally? Interested now? :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
Warren Young, on Saturday, February 9, 2019 01:36 PM, wrote... >On Feb 9, 2019, at 8:49 AM, Jose Isaias Cabrera wrote: >> >> Perhaps, Dr. Hipp and the team can write a quick PRAGMA that can do >> something like this, > >You may be interested in the sqldiff program that comes with SQLite: > >$ sqldiff --help >Usage: sqldiff [options] DB1 DB2 >Output SQL text that would transform DB1 into DB2. >Options: > --changeset FILE Write a CHANGESET into FILE > -L|--lib LIBRARY Load an SQLite extension library > --primarykey Use schema-defined PRIMARY KEYs > --rbu Output SQL to create/populate RBU table(s) > --schema Show only differences in the schema > --summary Show only a summary of the differences > --table TAB Show only differences in table TAB > --transaction Show SQL output inside a transaction > >In this case, you want the --table option. Yeah, I know about it, but I thought it was for two different DBs. Hmmm... I will check it out. Although, to check the difference on a table with a huge database with the DB1 being the same as DB2, I don't know how it will work, but, I will give it a try. Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
On Feb 9, 2019, at 8:49 AM, Jose Isaias Cabrera wrote: > > Perhaps, Dr. Hipp and the team can write a quick PRAGMA that can do something > like this, You may be interested in the sqldiff program that comes with SQLite: $ sqldiff --help Usage: sqldiff [options] DB1 DB2 Output SQL text that would transform DB1 into DB2. Options: --changeset FILE Write a CHANGESET into FILE -L|--lib LIBRARY Load an SQLite extension library --primarykey Use schema-defined PRIMARY KEYs --rbu Output SQL to create/populate RBU table(s) --schema Show only differences in the schema --summary Show only a summary of the differences --table TAB Show only differences in table TAB --transaction Show SQL output inside a transaction In this case, you want the --table option. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
WOW Clemens Ladisch on Saturday, February 9, 2019 11:44 AM wrote, Jose Isaias Cabrera wrote: >> t_20190208 (a PRIMARY KEY, b, c, d, e). >> >> I create a new table, >> >> t (a PRIMARY KEY, b, c, d, e) >> >> and insert a set of "new data", which contains changes that happened since >> yesterday >> after the new set of data was created. Right now, I bring the data out into >> two arrays >> and check for the data outside SQLite by iterating through the fields and >> checking for >> differences, one record at a time, but is there an easier or simpler way >> using SQLite >> commands? > >This query returns all rows that are new or changed: > > SELECT * FROM t > EXCEPT > SELECT * FROM t_20190208; wow, that's so simple. Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
Jose Isaias Cabrera wrote: > t_20190208 (a PRIMARY KEY, b, c, d, e). > > I create a new table, > > t (a PRIMARY KEY, b, c, d, e) > > and insert a set of "new data", which contains changes that happened since > yesterday > after the new set of data was created. Right now, I bring the data out into > two arrays > and check for the data outside SQLite by iterating through the fields and > checking for > differences, one record at a time, but is there an easier or simpler way > using SQLite > commands? This query returns all rows that are new or changed: SELECT * FROM t EXCEPT SELECT * FROM t_20190208; Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
Simon Slavin on Saturday, February 9, 2019 11:02 AM wrote... On 9 Feb 2019, at 3:49pm, Jose Isaias Cabrera wrote: > Imagine this scenario:I have this table, > > t (a PRIMARY KEY, b, c, d, e) > > that contains yesterday's data. Today, I rename that table to, > > t_20190208 (a PRIMARY KEY, b, c, d, e). > That's not how you use tables. Your software shouldn't be automatically > creating or dropping > tables during the life of your program. True, but there is a lot more details, but you're right in a very decent world. :-) > Have one table for your data for all days. > Add an extra column to the table for the datestamp. > Then to select all data for a particular day you do > > SELECT * From t WHERE datestamp = "20190208" We already have that. The problem is that we need to check differences of data coming in for different days, weeks and months. Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking differences in tables
On 9 Feb 2019, at 3:49pm, Jose Isaias Cabrera wrote: > Imagine this scenario:I have this table, > > t (a PRIMARY KEY, b, c, d, e) > > that contains yesterday's data. Today, I rename that table to, > > t_20190208 (a PRIMARY KEY, b, c, d, e). That's not how you use tables. Your software shouldn't be automatically creating or dropping tables during the life of your program. Have one table for your data for all days. Add an extra column to the table for the datestamp. Then to select all data for a particular day you do SELECT * From t WHERE datestamp = "20190208" If you only ever need to keep two days worth of data, then instead of a datestamp column you can use one which contains the text 'yesterday' or 'today'. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Checking differences in tables
Hi Gurus. Imagine this scenario:I have this table, t (a PRIMARY KEY, b, c, d, e) that contains yesterday's data. Today, I rename that table to, t_20190208 (a PRIMARY KEY, b, c, d, e). I create a new table, t (a PRIMARY KEY, b, c, d, e) and insert a set of "new data", which contains changes that happened since yesterday after the new set of data was created. Right now, I bring the data out into two arrays and check for the data outside SQLite by iterating through the fields and checking for differences, one record at a time, but is there an easier or simpler way using SQLite commands? The output could be something like, field | t_20190208 | t b | 2000 | 2100 e | week | daily etc. Perhaps, Dr. Hipp and the team can write a quick PRAGMA that can do something like this, PRAGMA cktablediff t_20190208 t table_name; Then one can just call SELECT * FROM table_name; Me and my dreams... He he he... josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
