Checking differences in tables

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

Checking differences in tables

Jose Isaias Cabrera-4

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

Re: Checking differences in tables

Simon Slavin-3
On 9 Feb 2019, at 3:49pm, Jose Isaias Cabrera <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Checking differences in tables

Jose Isaias Cabrera-4

Simon Slavin on Saturday, February 9, 2019 11:02 AM wrote...
On 9 Feb 2019, at 3:49pm, Jose Isaias Cabrera <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Checking differences in tables

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

Re: Checking differences in tables

Jose Isaias Cabrera-4

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

Re: Checking differences in tables

Warren Young
In reply to this post by Jose Isaias Cabrera-4
On Feb 9, 2019, at 8:49 AM, Jose Isaias Cabrera <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Checking differences in tables

Jose Isaias Cabrera-4

Warren Young, on Saturday, February 9, 2019 01:36 PM, wrote...

>On Feb 9, 2019, at 8:49 AM, Jose Isaias Cabrera <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Checking differences in tables

Warren Young
On Feb 9, 2019, at 12:20 PM, Jose Isaias Cabrera <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Checking differences in tables

Jose Isaias Cabrera-4

Warren Young, on Saturday, February 9, 2019 06:15 PM, wrote...

>On Feb 9, 2019, at 12:20 PM, Jose Isaias Cabrera <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Checking differences in tables

Jose Isaias Cabrera-4
In reply to this post by Simon Slavin-3


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

Re: Checking differences in tables

James K. Lowden
On Tue, 12 Feb 2019 15:05:29 +0000
Jose Isaias Cabrera <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Checking differences in tables

Jose Isaias Cabrera-4

James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote...

>On Tue, 12 Feb 2019 15:05:29 +0000
>Jose Isaias Cabrera <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Checking differences in tables

Jose Isaias Cabrera-4

>James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote...
>>On Tue, 12 Feb 2019 15:05:29 +0000
>>Jose Isaias Cabrera <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Checking differences in tables

David Raymond
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:[hidden email]] 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 +0000
>>Jose Isaias Cabrera <[hidden email]> 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
[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: Checking differences in tables

Jose Isaias Cabrera-4



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:[hidden email]] 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 +0000
>>Jose Isaias Cabrera <[hidden email]> 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
[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: Checking differences in tables

Kees Nuyt
On Tue, 12 Feb 2019 21:03:47 +0000, 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Checking differences in tables

Jose Isaias Cabrera-4
Kees Nuyt, on Tuesday, February 12, 2019 07:55 PM, wrote...

>On Tue, 12 Feb 2019 21:03:47 +0000, 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Checking differences in tables

Keith Medcalf
In reply to this post by Kees Nuyt

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-
>[hidden email]] On Behalf Of Kees Nuyt
>Sent: Tuesday, 12 February, 2019 17:55
>To: [hidden email]
>Subject: Re: [sqlite] Checking differences in tables
>
>On Tue, 12 Feb 2019 21:03:47 +0000, 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
>[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: Checking differences in tables

Jose Isaias Cabrera-4

Man, you guys are so smart... Thanks, Keith.

From: sqlite-users <[hidden email]> on behalf of Keith Medcalf <[hidden email]>
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-
>[hidden email]] On Behalf Of Kees Nuyt
>Sent: Tuesday, 12 February, 2019 17:55
>To: [hidden email]
>Subject: Re: [sqlite] Checking differences in tables
>
>On Tue, 12 Feb 2019 21:03:47 +0000, 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
>[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: Checking differences in tables

Jose Isaias Cabrera-4
In reply to this post by James K. Lowden

James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote...

>On Tue, 12 Feb 2019 15:05:29 +0000
>Jose Isaias Cabrera <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12