Re: [sqlite] Query to compare two sqlite databases
See hash-based tool for signature/delta calculation and replication here: http://sqlite.mobigroup.ru/wiki?name=sqlite3-rdiff http://sqlite.mobigroup.ru/wiki?name=sqlite3-rdiffdelta database has only differences beetween two databases. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query to compare two sqlite databases
Hello, The following query works for me. select * from db2.table where not exists (select * from db1.table where db1.table.column1 = db2.table.column1); Thanks again for the assistance. --- On Fri, 9/24/10, Oliver Peters oliver@web.de wrote: From: Oliver Peters oliver@web.de Subject: Re: [sqlite] Query to compare two sqlite databases To: sqlite-users@sqlite.org Date: Friday, September 24, 2010, 2:54 PM Oliver Peters oliver@... writes: sry the result is what is not in table01 I meant: the result is what is in table01 but NOT in table02 Oliver ___ 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] Query to compare two sqlite databases
Hello, I compare an application every few days which has sqlite as it's client database. I look at the content and check whether documents have made it into the application after it has been built. I also check the metadata in the sqlite client database for changes. So, I am constantly comparing the last database with the newer database. Both databases have exactly the same tables, with only the data being changed in most of the 51 tables.The largest table has about 3,700,000 rows. Most other tables have much less rows in them. Could someone suggest an sql query to find the difference in the same table (ta) for both the last database (db1) and the newer database (db2)? I can use SQLiteSpy to connect and attach to the databases. Thanks for any help to point me in the direction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query to compare two sqlite databases
On Fri, 24 Sep 2010, John Reed wrote: I compare an application every few days which has sqlite as it's client database. I look at the content and check whether documents have made it into the application after it has been built. I also check the metadata in the sqlite client database for changes. So, I am constantly comparing the last database with the newer database. Both databases have exactly the same tables, with only the data being changed in most of the 51 tables.The largest table has about 3,700,000 rows. Most other tables have much less rows in them. Could someone suggest an sql query to find the difference in the same table (ta) for both the last database (db1) and the newer database (db2)? I can use SQLiteSpy to connect and attach to the databases. You'll want to tune the syntax, but try something like: SELECT colA FROM db1 WHERE (NOT EXIST colA IN db2); The idea is to match rows in each table and where the equivalent row in db1 is not in db2, add that to the results table. Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query to compare two sqlite databases
On 24-09-10 21:06, Rich Shepard wrote: On Fri, 24 Sep 2010, John Reed wrote: I compare an application every few days which has sqlite as it's client database. I look at the content and check whether documents have made it into the application after it has been built. I also check the metadata in the sqlite client database for changes. So, I am constantly comparing the last database with the newer database. Both databases have exactly the same tables, with only the data being changed in most of the 51 tables.The largest table has about 3,700,000 rows. Most other tables have much less rows in them. Could someone suggest an sql query to find the difference in the same table (ta) for both the last database (db1) and the newer database (db2)? I can use SQLiteSpy to connect and attach to the databases. You'll want to tune the syntax, but try something like: SELECT colA FROM db1 WHERE (NOT EXIST colA IN db2); The idea is to match rows in each table and where the equivalent row in db1 is not in db2, add that to the results table. Rich you mean something like: select id1, id2 from callprog a where id1 not in (select b.id1 FROM callprog b where b.id1=a.id1 ); But what is there is more than 1 column? it will grow in complexity when you have a lot of columns. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query to compare two sqlite databases
On Fri, 24 Sep 2010, luuk34 wrote: you mean something like: select id1, id2 from callprog a where id1 not in (select b.id1 FROM callprog b where b.id1=a.id1 ); Yeah; much better. But what is there is more than 1 column? it will grow in complexity when you have a lot of columns. My understanding of the original problem was that rows were about the same, so finding a common column in db1 which was not in db2 was the need. As long as there is one unique column common to both tables it makes no difference how many other columns there are in each one. Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query to compare two sqlite databases
Hello, Thanks for your response. I'm almost there but I keep getting sql syntax error (near col1) with the last part of the query where(not exists in db2); Here's where I'm at: select 'db1', db1.table1.col1, 'db2', db2.table2.col1 from db1.table1, db2.table2 WHERE (NOT EXISTS col1 IN db2); Thanks. --- On Fri, 9/24/10, Rich Shepard rshep...@appl-ecosys.com wrote: From: Rich Shepard rshep...@appl-ecosys.com Subject: Re: [sqlite] Query to compare two sqlite databases To: General Discussion of SQLite Database sqlite-users@sqlite.org Date: Friday, September 24, 2010, 12:51 PM On Fri, 24 Sep 2010, luuk34 wrote: you mean something like: select id1, id2 from callprog a where id1 not in (select b.id1 FROM callprog b where b.id1=a.id1 ); Yeah; much better. But what is there is more than 1 column? it will grow in complexity when you have a lot of columns. My understanding of the original problem was that rows were about the same, so finding a common column in db1 which was not in db2 was the need. As long as there is one unique column common to both tables it makes no difference how many other columns there are in each one. Rich ___ 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] Query to compare two sqlite databases
Am 24.09.2010 20:41, schrieb John Reed: Hello, I compare an application every few days which has sqlite as it's client database. I look at the content and check whether documents have made it into the application after it has been built. I also check the metadata in the sqlite client database for changes. So, I am constantly comparing the last database with the newer database. Both databases have exactly the same tables, with only the data being changed in most of the 51 tables.The largest table has about 3,700,000 rows. Most other tables have much less rows in them. Could someone suggest an sql query to find the difference in the same table (ta) for both the last database (db1) and the newer database (db2)? I can use SQLiteSpy to connect and attach to the databases. use EXCEPT SELECT col01, col02,... FROM table01 EXCEPT SELECT col01, col02,... FROM table02 ; table01 is the newest table while table02 is its predecessor the result is what is not in table01 greetings Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query to compare two sqlite databases
Oliver Peters oliver@... writes: sry the result is what is not in table01 I meant: the result is what is in table01 but NOT in table02 Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users