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 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
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 To: Fehmi Noyan ISI ; General Discussion of SQLite Database 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 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
Re: [sqlite] Comparing two tables column by column
On 29 Jul 2013, at 4:03am, Fehmi Noyan ISI 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 wrote: > > On 29 Jul 2013, at 4:03am, Fehmi Noyan ISI 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 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 wrote: > Simon Slavin wrote: > > On 29 Jul 2013, at 4:03am, Fehmi Noyan ISI 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 wrote: > Simon Slavin wrote: > > On 29 Jul 2013, at 4:03am, Fehmi Noyan ISI 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 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 To: General Discussion of SQLite Database 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 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 : >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 >To: General Discussion of SQLite Database >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 > 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" To: Fehmi Noyan ISI ; General Discussion of SQLite Database 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 : >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 >To: General Discussion of SQLite Database >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 > 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
That can work, if performance is of no concern. Otherwise it will become miserably slow. Von: Fehmi Noyan ISI mailto:fnoyan...@yahoo.com>> Antworten an: Fehmi Noyan ISI mailto:fnoyan...@yahoo.com>> Datum: Dienstag, 30. Juli 2013 01:54 An: Benjamin Stadin mailto:benjamin.sta...@heidelberg-mobil.com>>, General Discussion of SQLite Database mailto: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" mailto:benjamin.sta...@heidelberg-mobil.com>> To: Fehmi Noyan ISI mailto:fnoyan...@yahoo.com>>; General Discussion of SQLite Database mailto: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 mailto: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 mailto:slav...@bigfraud.org>> >To: General Discussion of SQLite Database >mailto: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 >mailto: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<mailto:sqlite-users@sqlite.org> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >___ >sqlite-users mailing list >sqlite-users@sqlite.org<mailto: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" wrote: > That can work, if performance is of no concern. Otherwise it will become > miserably slow. > > > Von: Fehmi Noyan ISI > Antworten an: Fehmi Noyan ISI > Datum: Dienstag, 30. Juli 2013 01:54 > An: Benjamin Stadin , General > Discussion of SQLite Database > 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" > To: Fehmi Noyan ISI ; General Discussion of SQLite > Database > 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 : > > >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 > >To: General Discussion of SQLite Database > >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 > > 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 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" wrote: That can work, if performance is of no concern. Otherwise it will become miserably slow. Von: Fehmi Noyan ISI Antworten an: Fehmi Noyan ISI Datum: Dienstag, 30. Juli 2013 01:54 An: Benjamin Stadin , General Discussion of SQLite Database 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" To: Fehmi Noyan ISI ; General Discussion of SQLite Database 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 : 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 To: General Discussion of SQLite Database 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 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