Good day, I'd like to tack on an additional question, since it is a common case applied to the goal of this thread.
Suppose you have a field that time stamps the date each company's data was entered. Unfortunately some records in old2.db have newer contact information, while others are older than in old1.db. Since companies sometimes change their address the one with the latest 'date_Entered' should be the one left in the merged database. One way to do this is similar to ... 1) Insert into new.db from old1.db (as previously posted) 2) delete from new.db newdb where newdb.company in( select company from old1.db) and newdb.date_entered < old1.db.date_entered (/*syntax errors likely present here*/) 3) insert from old3.db where not in new.db (as previously posted) Can 2) be done with an UPDATE instead? Is there a better way than even this? I presume this would be easily adapted back to the case of just updating db1 with new or newer records in db2. regards, Adam DeVita On Fri, Mar 13, 2009 at 11:24 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > "Gilles Ganault" <gilles.gana...@free.fr> wrote in > message news:bcqkr45c944gv4g1h9ovpjq1ood1i1v...@4ax.com >> On Fri, 13 Mar 2009 09:52:25 -0400, "Igor Tandetnik" >> <itandet...@mvps.org> wrote: >> Thank you Igor for the help. Before I give it a shot, I need to speciy >> those requirements: >> 1. The tables live in two SQLite database files, so I must open both >> in the same client session > > http://sqlite.org/lang_attach.html > >> 2. Each table may contain one or more records of the same company >> >> The goal is to create a third, new database file where companies are >> unique, ie. a single record for each company. > > .open new.db > ATTACH 'old1.db' as old1; > ATTACH 'old2.db' as old2; > > create table companies(...); > > insert into companies > select * from old1.companies where rowid in > (select min(rowid) from old1.companies > group by company); > > insert into companies > select * from old2.companies where rowid in > (select min(rowid) from old2.companies > where company not in (select company from companies) > group by company); > > Igor Tandetnik > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
