Re: [sqlite] Comparing two tables column by column
On 30.07.2013 14:49, fnoyan...@yahoo.com wrote:
Yes, it is slow actually. But I could not do it with SQL. I wish I could do
more with SQL than code, which would give a good performance improvement.
From overall application point of view, I may be considering using threads to
read from database, so that the performance will be improved a bit.
On 30/07/2013, at 4:26 PM, Stadin, Benjamin
benjamin.sta...@heidelberg-mobil.com wrote:
That can work, if performance is of no concern. Otherwise it will become
miserably slow.
Von: Fehmi Noyan ISI fnoyan...@yahoo.com
Antworten an: Fehmi Noyan ISI fnoyan...@yahoo.com
Datum: Dienstag, 30. Juli 2013 01:54
An: Benjamin Stadin benjamin.sta...@heidelberg-mobil.com, General Discussion of
SQLite Database sqlite-users@sqlite.org
Betreff: Re: [sqlite] Comparing two tables column by column
The approach I am using to compare tableA_old and tableA_new is;
typedef struct container_t {
// a structure to pass parameters into callbacks
} container;
static int callback_2(){
// compare two values in the resulting table if they are different
// write them into a resulting file
}
static int callback_1(){
for (each column){
char sql[256];
sprintf(sql,select %s from tableA_old where
pkey=%s,columnName,primarykey);
sqlite3_exec(db,sql,callback_2,,)
}
}
int main(){
// import CS file into DB
// for each pair of files (each file is a table and files have
// the same column names in the same order). Actually, these are
// records for different dates
char sql[256] = select * from tableA_new;;
container c;
sqlite3_exec(db,sql,callback_1,(void*)c,null)
}
From: Stadin, Benjamin benjamin.sta...@heidelberg-mobil.com
To: Fehmi Noyan ISI fnoyan...@yahoo.com; General Discussion of SQLite Database
sqlite-users@sqlite.org
Sent: Tuesday, July 30, 2013 9:00 AM
Subject: Re: [sqlite] Comparing two tables column by column
If you like ruby, I have another idea to get you going (maybe without
needing to write much code):
- Use a registered function to SQLite to create MD5 or SHA1 keys for rows
in the table. Here is a ruby snippet that registers a SHA1 function:
http://www.copiousfreetime.org/articles/2009/01/10/writing-sql-functions-in
-ruby.html
- Join both tables by your primary key and select those where the SHA keys
don't match
- go threw each column programmatically in your ruby code and simply
compare column values with each other
If you then need performance and get by some means a trigger for your
newly inserted records, add a sha1 field to all of your tables and
precompute and index the sha1 for each table.
Benjamin Stadin
Am 30.07.13 01:07 schrieb Fehmi Noyan ISI unter fnoyan...@yahoo.com:
EXCEPT query gave the different rows in tables, but what I am after is
the different values for existing records.
The column names are exactly the same, however the number of rows may
differ (with most of the records are the same).
From: Simon Slavin slav...@bigfraud.org
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Sent: Monday, July 29, 2013 9:10 PM
Subject: Re: [sqlite] Comparing two tables column by column
On 29 Jul 2013, at 12:36pm, Fabian Klebert
f.kleb...@klebert-engineering.de wrote:
Wouldn't
SELECT * FROM table1
EXCEPT
SELECT * FROM table2
solve this problem?
I think it does for the example provided. Not sure if it would work in
real-world environment.
There are two elements: making sure the same rows are present, and making
sure the contents of the rows match. I would probably use EXCEPT
commands to find out entries in one database which weren't in the other,
then use INTERSECT command to check that the fields in the rows which
were in both. But that's just at first glance.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
a) Store your table backups ordered by a primary key (not rowid but
integer primary key).
b) Make a whole table select ordered by the same primary key for the
current table.
c) Merge both tables.
This will be faster cause you won't need to trace through the BTREE
(B+TREE) for each recordset.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing two tables column by column
That can work, if performance is of no concern. Otherwise it will become
miserably slow.
Von: Fehmi Noyan ISI fnoyan...@yahoo.commailto:fnoyan...@yahoo.com
Antworten an: Fehmi Noyan ISI fnoyan...@yahoo.commailto:fnoyan...@yahoo.com
Datum: Dienstag, 30. Juli 2013 01:54
An: Benjamin Stadin
benjamin.sta...@heidelberg-mobil.commailto:benjamin.sta...@heidelberg-mobil.com,
General Discussion of SQLite Database
sqlite-users@sqlite.orgmailto:sqlite-users@sqlite.org
Betreff: Re: [sqlite] Comparing two tables column by column
The approach I am using to compare tableA_old and tableA_new is;
typedef struct container_t {
// a structure to pass parameters into callbacks
} container;
static int callback_2(){
// compare two values in the resulting table if they are different
// write them into a resulting file
}
static int callback_1(){
for (each column){
char sql[256];
sprintf(sql,select %s from tableA_old where
pkey=%s,columnName,primarykey);
sqlite3_exec(db,sql,callback_2,,)
}
}
int main(){
// import CS file into DB
// for each pair of files (each file is a table and files have
// the same column names in the same order). Actually, these are
// records for different dates
char sql[256] = select * from tableA_new;;
container c;
sqlite3_exec(db,sql,callback_1,(void*)c,null)
}
From: Stadin, Benjamin
benjamin.sta...@heidelberg-mobil.commailto:benjamin.sta...@heidelberg-mobil.com
To: Fehmi Noyan ISI fnoyan...@yahoo.commailto:fnoyan...@yahoo.com; General
Discussion of SQLite Database
sqlite-users@sqlite.orgmailto:sqlite-users@sqlite.org
Sent: Tuesday, July 30, 2013 9:00 AM
Subject: Re: [sqlite] Comparing two tables column by column
If you like ruby, I have another idea to get you going (maybe without
needing to write much code):
- Use a registered function to SQLite to create MD5 or SHA1 keys for rows
in the table. Here is a ruby snippet that registers a SHA1 function:
http://www.copiousfreetime.org/articles/2009/01/10/writing-sql-functions-in
-ruby.html
- Join both tables by your primary key and select those where the SHA keys
don't match
- go threw each column programmatically in your ruby code and simply
compare column values with each other
If you then need performance and get by some means a trigger for your
newly inserted records, add a sha1 field to all of your tables and
precompute and index the sha1 for each table.
Benjamin Stadin
Am 30.07.13 01:07 schrieb Fehmi Noyan ISI unter
fnoyan...@yahoo.commailto:fnoyan...@yahoo.com:
EXCEPT query gave the different rows in tables, but what I am after is
the different values for existing records.
The column names are exactly the same, however the number of rows may
differ (with most of the records are the same).
From: Simon Slavin slav...@bigfraud.orgmailto:slav...@bigfraud.org
To: General Discussion of SQLite Database
sqlite-users@sqlite.orgmailto:sqlite-users@sqlite.org
Sent: Monday, July 29, 2013 9:10 PM
Subject: Re: [sqlite] Comparing two tables column by column
On 29 Jul 2013, at 12:36pm, Fabian Klebert
f.kleb...@klebert-engineering.demailto:f.kleb...@klebert-engineering.de
wrote:
Wouldn't
SELECT * FROM table1
EXCEPT
SELECT * FROM table2
solve this problem?
I think it does for the example provided. Not sure if it would work in
real-world environment.
There are two elements: making sure the same rows are present, and making
sure the contents of the rows match. I would probably use EXCEPT
commands to find out entries in one database which weren't in the other,
then use INTERSECT command to check that the fields in the rows which
were in both. But that's just at first glance.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.orgmailto:sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.orgmailto:sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing two tables column by column
Yes, it is slow actually. But I could not do it with SQL. I wish I could do
more with SQL than code, which would give a good performance improvement.
From overall application point of view, I may be considering using threads to
read from database, so that the performance will be improved a bit.
On 30/07/2013, at 4:26 PM, Stadin, Benjamin
benjamin.sta...@heidelberg-mobil.com wrote:
That can work, if performance is of no concern. Otherwise it will become
miserably slow.
Von: Fehmi Noyan ISI fnoyan...@yahoo.com
Antworten an: Fehmi Noyan ISI fnoyan...@yahoo.com
Datum: Dienstag, 30. Juli 2013 01:54
An: Benjamin Stadin benjamin.sta...@heidelberg-mobil.com, General
Discussion of SQLite Database sqlite-users@sqlite.org
Betreff: Re: [sqlite] Comparing two tables column by column
The approach I am using to compare tableA_old and tableA_new is;
typedef struct container_t {
// a structure to pass parameters into callbacks
} container;
static int callback_2(){
// compare two values in the resulting table if they are different
// write them into a resulting file
}
static int callback_1(){
for (each column){
char sql[256];
sprintf(sql,select %s from tableA_old where
pkey=%s,columnName,primarykey);
sqlite3_exec(db,sql,callback_2,,)
}
}
int main(){
// import CS file into DB
// for each pair of files (each file is a table and files have
// the same column names in the same order). Actually, these are
// records for different dates
char sql[256] = select * from tableA_new;;
container c;
sqlite3_exec(db,sql,callback_1,(void*)c,null)
}
From: Stadin, Benjamin benjamin.sta...@heidelberg-mobil.com
To: Fehmi Noyan ISI fnoyan...@yahoo.com; General Discussion of SQLite
Database sqlite-users@sqlite.org
Sent: Tuesday, July 30, 2013 9:00 AM
Subject: Re: [sqlite] Comparing two tables column by column
If you like ruby, I have another idea to get you going (maybe without
needing to write much code):
- Use a registered function to SQLite to create MD5 or SHA1 keys for rows
in the table. Here is a ruby snippet that registers a SHA1 function:
http://www.copiousfreetime.org/articles/2009/01/10/writing-sql-functions-in
-ruby.html
- Join both tables by your primary key and select those where the SHA keys
don't match
- go threw each column programmatically in your ruby code and simply
compare column values with each other
If you then need performance and get by some means a trigger for your
newly inserted records, add a sha1 field to all of your tables and
precompute and index the sha1 for each table.
Benjamin Stadin
Am 30.07.13 01:07 schrieb Fehmi Noyan ISI unter fnoyan...@yahoo.com:
EXCEPT query gave the different rows in tables, but what I am after is
the different values for existing records.
The column names are exactly the same, however the number of rows may
differ (with most of the records are the same).
From: Simon Slavin slav...@bigfraud.org
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Sent: Monday, July 29, 2013 9:10 PM
Subject: Re: [sqlite] Comparing two tables column by column
On 29 Jul 2013, at 12:36pm, Fabian Klebert
f.kleb...@klebert-engineering.de wrote:
Wouldn't
SELECT * FROM table1
EXCEPT
SELECT * FROM table2
solve this problem?
I think it does for the example provided. Not sure if it would work in
real-world environment.
There are two elements: making sure the same rows are present, and making
sure the contents of the rows match. I would probably use EXCEPT
commands to find out entries in one database which weren't in the other,
then use INTERSECT command to check that the fields in the rows which
were in both. But that's just at first glance.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing two tables column by column
On 29 Jul 2013, at 4:03am, Fehmi Noyan ISI fnoyan...@yahoo.com wrote: One point I forgot to mention; the number of columns is unknown. There is no way in SQL to say Give me the contents of all the columns of a row of table in an unambiguous format.. It would be possible to write the code you want in SQLite, by using various PRAGMAs to inspect the table format, but this would lead to complicated code which was highly tuned to how SQLite works. I suspect you'd be better off writing C code which inspects whatever SELECT *' returns. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing two tables column by column
Yes, it turned out that achieving the goal with C code is much simpler than using SQL statements (I also take my limited sql knowledge into account) Now, I'll have two sqlite3_exec() calls, one of which is invoked by first call's callback function. This led having some natsy C structs around to pass parameters to sqlite3_exec() calls, but I could not find any other approach. On 29/07/2013, at 7:48 PM, Simon Slavin slav...@bigfraud.org wrote: On 29 Jul 2013, at 4:03am, Fehmi Noyan ISI fnoyan...@yahoo.com wrote: One point I forgot to mention; the number of columns is unknown. There is no way in SQL to say Give me the contents of all the columns of a row of table in an unambiguous format.. It would be possible to write the code you want in SQLite, by using various PRAGMAs to inspect the table format, but this would lead to complicated code which was highly tuned to how SQLite works. I suspect you'd be better off writing C code which inspects whatever SELECT *' returns. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing two tables column by column
Simon Slavin wrote: On 29 Jul 2013, at 4:03am, Fehmi Noyan ISI fnoyan...@yahoo.com wrote: One point I forgot to mention; the number of columns is unknown. There is no way in SQL to say Give me the contents of all the columns of a row of table in an unambiguous format.. Well, just give me could be done with SELECT *, but it is almost impossible to compare such columns in SQL. If the tables have the same number _and_ names of columns, it would be possible to use a NATURAL JOIN, and combine this with an outer join to get non-matching records: SELECT table1.* FROM table1 NATURAL LEFT JOIN table2 WHERE table2.rowid IS NULL This will return those records from table1 that do not have a matching record in table2. For the other direction, run the same query with table1 and table2 exchanged. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing two tables column by column
To be fair to me, the example had the same column names. If the two tables have the same column names, then having a bit of extra code to tag on the column name + _1 might have worked. As my first reply answered, untested. ;) On Mon, Jul 29, 2013 at 6:46 AM, Clemens Ladisch clem...@ladisch.de wrote: Simon Slavin wrote: On 29 Jul 2013, at 4:03am, Fehmi Noyan ISI fnoyan...@yahoo.com wrote: One point I forgot to mention; the number of columns is unknown. There is no way in SQL to say Give me the contents of all the columns of a row of table in an unambiguous format.. Well, just give me could be done with SELECT *, but it is almost impossible to compare such columns in SQL. If the tables have the same number _and_ names of columns, it would be possible to use a NATURAL JOIN, and combine this with an outer join to get non-matching records: SELECT table1.* FROM table1 NATURAL LEFT JOIN table2 WHERE table2.rowid IS NULL This will return those records from table1 that do not have a matching record in table2. For the other direction, run the same query with table1 and table2 exchanged. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing two tables column by column
Wouldn't SELECT * FROM table1 EXCEPT SELECT * FROM table2 solve this problem? I think it does for the example provided. Not sure if it would work in real-world environment. -Ursprüngliche Nachricht- Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] Im Auftrag von Stephen Chrzanowski Gesendet: Montag, 29. Juli 2013 13:01 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Comparing two tables column by column To be fair to me, the example had the same column names. If the two tables have the same column names, then having a bit of extra code to tag on the column name + _1 might have worked. As my first reply answered, untested. ;) On Mon, Jul 29, 2013 at 6:46 AM, Clemens Ladisch clem...@ladisch.de wrote: Simon Slavin wrote: On 29 Jul 2013, at 4:03am, Fehmi Noyan ISI fnoyan...@yahoo.com wrote: One point I forgot to mention; the number of columns is unknown. There is no way in SQL to say Give me the contents of all the columns of a row of table in an unambiguous format.. Well, just give me could be done with SELECT *, but it is almost impossible to compare such columns in SQL. If the tables have the same number _and_ names of columns, it would be possible to use a NATURAL JOIN, and combine this with an outer join to get non-matching records: SELECT table1.* FROM table1 NATURAL LEFT JOIN table2 WHERE table2.rowid IS NULL This will return those records from table1 that do not have a matching record in table2. For the other direction, run the same query with table1 and table2 exchanged. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing two tables column by column
On 29 Jul 2013, at 12:36pm, Fabian Klebert f.kleb...@klebert-engineering.de wrote: Wouldn't SELECT * FROM table1 EXCEPT SELECT * FROM table2 solve this problem? I think it does for the example provided. Not sure if it would work in real-world environment. There are two elements: making sure the same rows are present, and making sure the contents of the rows match. I would probably use EXCEPT commands to find out entries in one database which weren't in the other, then use INTERSECT command to check that the fields in the rows which were in both. But that's just at first glance. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing two tables column by column
EXCEPT query gave the different rows in tables, but what I am after is the different values for existing records. The column names are exactly the same, however the number of rows may differ (with most of the records are the same). From: Simon Slavin slav...@bigfraud.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Monday, July 29, 2013 9:10 PM Subject: Re: [sqlite] Comparing two tables column by column On 29 Jul 2013, at 12:36pm, Fabian Klebert f.kleb...@klebert-engineering.de wrote: Wouldn't SELECT * FROM table1 EXCEPT SELECT * FROM table2 solve this problem? I think it does for the example provided. Not sure if it would work in real-world environment. There are two elements: making sure the same rows are present, and making sure the contents of the rows match. I would probably use EXCEPT commands to find out entries in one database which weren't in the other, then use INTERSECT command to check that the fields in the rows which were in both. But that's just at first glance. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing two tables column by column
If you like ruby, I have another idea to get you going (maybe without needing to write much code): - Use a registered function to SQLite to create MD5 or SHA1 keys for rows in the table. Here is a ruby snippet that registers a SHA1 function: http://www.copiousfreetime.org/articles/2009/01/10/writing-sql-functions-in -ruby.html - Join both tables by your primary key and select those where the SHA keys don't match - go threw each column programmatically in your ruby code and simply compare column values with each other If you then need performance and get by some means a trigger for your newly inserted records, add a sha1 field to all of your tables and precompute and index the sha1 for each table. Benjamin Stadin Am 30.07.13 01:07 schrieb Fehmi Noyan ISI unter fnoyan...@yahoo.com: EXCEPT query gave the different rows in tables, but what I am after is the different values for existing records. The column names are exactly the same, however the number of rows may differ (with most of the records are the same). From: Simon Slavin slav...@bigfraud.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Monday, July 29, 2013 9:10 PM Subject: Re: [sqlite] Comparing two tables column by column On 29 Jul 2013, at 12:36pm, Fabian Klebert f.kleb...@klebert-engineering.de wrote: Wouldn't SELECT * FROM table1 EXCEPT SELECT * FROM table2 solve this problem? I think it does for the example provided. Not sure if it would work in real-world environment. There are two elements: making sure the same rows are present, and making sure the contents of the rows match. I would probably use EXCEPT commands to find out entries in one database which weren't in the other, then use INTERSECT command to check that the fields in the rows which were in both. But that's just at first glance. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing two tables column by column
The approach I am using to compare tableA_old and tableA_new is;
typedef struct container_t {
// a structure to pass parameters into callbacks
} container;
static int callback_2(){
// compare two values in the resulting table if they are different
// write them into a resulting file
}
static int callback_1(){
for (each column){
char sql[256];
sprintf(sql,select %s from tableA_old where
pkey=%s,columnName,primarykey);
sqlite3_exec(db,sql,callback_2,,)
}
}
int main(){
// import CS file into DB
//for each pair of files (each file is a table and files have
// the same column names in the same order). Actually, these are
// records for different dates
char sql[256] = select * from tableA_new;;
container c;
sqlite3_exec(db,sql,callback_1,(void*)c,null)
}
From: Stadin, Benjamin benjamin.sta...@heidelberg-mobil.com
To: Fehmi Noyan ISI fnoyan...@yahoo.com; General Discussion of SQLite
Database sqlite-users@sqlite.org
Sent: Tuesday, July 30, 2013 9:00 AM
Subject: Re: [sqlite] Comparing two tables column by column
If you like ruby, I have another idea to get you going (maybe without
needing to write much code):
- Use a registered function to SQLite to create MD5 or SHA1 keys for rows
in the table. Here is a ruby snippet that registers a SHA1 function:
http://www.copiousfreetime.org/articles/2009/01/10/writing-sql-functions-in
-ruby.html
- Join both tables by your primary key and select those where the SHA keys
don't match
- go threw each column programmatically in your ruby code and simply
compare column values with each other
If you then need performance and get by some means a trigger for your
newly inserted records, add a sha1 field to all of your tables and
precompute and index the sha1 for each table.
Benjamin Stadin
Am 30.07.13 01:07 schrieb Fehmi Noyan ISI unter fnoyan...@yahoo.com:
EXCEPT query gave the different rows in tables, but what I am after is
the different values for existing records.
The column names are exactly the same, however the number of rows may
differ (with most of the records are the same).
From: Simon Slavin slav...@bigfraud.org
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Sent: Monday, July 29, 2013 9:10 PM
Subject: Re: [sqlite] Comparing two tables column by column
On 29 Jul 2013, at 12:36pm, Fabian Klebert
f.kleb...@klebert-engineering.de wrote:
Wouldn't
SELECT * FROM table1
EXCEPT
SELECT * FROM table2
solve this problem?
I think it does for the example provided. Not sure if it would work in
real-world environment.
There are two elements: making sure the same rows are present, and making
sure the contents of the rows match. I would probably use EXCEPT
commands to find out entries in one database which weren't in the other,
then use INTERSECT command to check that the fields in the rows which
were in both. But that's just at first glance.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Comparing two tables column by column
Hi, I would like to compare two tables row by row having same primary keys. The comparison should take each row from TABLE1 and find relevant entry (based on KEY) from TABLE2 and compare value of EACH column. For the TABLE1 and TABLE2 below, the values of COL4 for KEY3 should be returned (both values). TABLE1 COL1 COL2 COL3 COL4 KEY1 X Y Z KEY2 A B C KEY3 K L M TABLE2 COL1 COL2 COL3 COL4 KEY1 X Y Z KEY2 A B C KEY3 K L D Is there a way to do this via SQL or I will try to do it with C loops (which ends up with nasty code!). Thanks in advance... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing two tables column by column
Untested select table1.col1 as Key, table1.col2 as T1C2, table1.col3 as T1C3, table1.col4 as T1C4, table2.col2 as T2C2, table2.col3 as T2C3, table2.col4 as T2C4 from Table1 join Table2 on Table1.Col1=Table2.Col2 order by table1.col1 On Sun, Jul 28, 2013 at 10:44 PM, Fehmi Noyan ISI fnoyan...@yahoo.comwrote: Hi, I would like to compare two tables row by row having same primary keys. The comparison should take each row from TABLE1 and find relevant entry (based on KEY) from TABLE2 and compare value of EACH column. For the TABLE1 and TABLE2 below, the values of COL4 for KEY3 should be returned (both values). TABLE1 COL1 COL2 COL3 COL4 KEY1 X Y Z KEY2 A B C KEY3 K L M TABLE2 COL1 COL2 COL3 COL4 KEY1 X Y Z KEY2 A B C KEY3 K L D Is there a way to do this via SQL or I will try to do it with C loops (which ends up with nasty code!). Thanks in advance... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing two tables column by column
Thanks for the reply... One point I forgot to mention; the number of columns is unknown... Actually there are more than 40-50 tables that needs to be examined. From: Stephen Chrzanowski pontia...@gmail.com To: Fehmi Noyan ISI fnoyan...@yahoo.com; General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Monday, July 29, 2013 12:31 PM Subject: Re: [sqlite] Comparing two tables column by column Untested select table1.col1 as Key, table1.col2 as T1C2, table1.col3 as T1C3, table1.col4 as T1C4, table2.col2 as T2C2, table2.col3 as T2C3, table2.col4 as T2C4 from Table1 join Table2 on Table1.Col1=Table2.Col2 order by table1.col1 On Sun, Jul 28, 2013 at 10:44 PM, Fehmi Noyan ISI fnoyan...@yahoo.com wrote: Hi, I would like to compare two tables row by row having same primary keys. The comparison should take each row from TABLE1 and find relevant entry (based on KEY) from TABLE2 and compare value of EACH column. For the TABLE1 and TABLE2 below, the values of COL4 for KEY3 should be returned (both values). TABLE1 COL1 COL2 COL3 COL4 KEY1 X Y Z KEY2 A B C KEY3 K L M TABLE2 COL1 COL2 COL3 COL4 KEY1 X Y Z KEY2 A B C KEY3 K L D Is there a way to do this via SQL or I will try to do it with C loops (which ends up with nasty code!). Thanks in advance... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
