Re: [sqlite] Merging two SQLites + leaving out duplicates?

[Igor Tandetnik]

"Adam DeVita"  wrote
in message
news:804081910903130900r6c615f02u2b72ba810cccf...@mail.gmail.com
> 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.

Try something like this:

select * from old1 x where not exists (
select 1 from old1 y
where x.company = y.company and x.date_entered < y.date_entered)
and not exists (
select 1 from old2 z
where x.company = z.company and x.date_entered < z.date_entered);

insert into new
select * from old2 x where not exists (
select 1 from old1 y
where x.company = y.company and x.date_entered < y.date_entered)
and not exists (
select 1 from old2 z
where x.company = z.company and x.date_entered < z.date_entered);

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Merging two SQLites + leaving out duplicates?

[Adam DeVita]

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  wrote:
> "Gilles Ganault"  wrote in
> message news:bcqkr45c944gv4g1h9ovpjq1ood1i1v...@4ax.com
>> On Fri, 13 Mar 2009 09:52:25 -0400, "Igor Tandetnik"
>>  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

Re: [sqlite] Merging two SQLites + leaving out duplicates?

[Igor Tandetnik]

"Gilles Ganault"  wrote in
message news:bcqkr45c944gv4g1h9ovpjq1ood1i1v...@4ax.com
> On Fri, 13 Mar 2009 09:52:25 -0400, "Igor Tandetnik"
>  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

Re: [sqlite] Merging two SQLites + leaving out duplicates?

[P Kishor]

On Fri, Mar 13, 2009 at 9:14 AM, Gilles Ganault  wrote:
> On Fri, 13 Mar 2009 09:52:25 -0400, "Igor Tandetnik"
>  wrote:
> (snip)
>
> 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
> 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.

assuming your "client" is the sqlite3 shell,

1. open db1 (new db)
2. attach db2 and db3 (old dbs)
3. follow Igor's directions

>
> Thank you.
>
>



-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Merging two SQLites + leaving out duplicates?

[Gilles Ganault]

Hello

I have two SQLites databases that I need to merge. Some companies are
found in both, so I'd like to only keep one record when this occurs.

What would be the right way to do this?

Thank you.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users