[mythtv-users] Possibly OT: Merging Tables, NO duplicates.

[George Mari]

R. G. Newbury wrote:
>   I am having a problem which I am sure has been addressed by someone 
> before now, and I don't wish to waste time re-inventing the wheel,
> 
> I have a small (presently command-line controlled) sqlite program to 
> keep track of time usage. It runs on 3 different physical computers. I 
> need to merge the entries, *without duplication*. All three units run 
> the same app and the tables are the same. But I want the entries I make 
> on the laptop and/or the Nokia tablet to end up merged into the 'master' 
> on the desktop.
> 
> At the moment I cannot see how I can select 'distinct' from 2 tables, so
> I *think* to do this, I will need to copy each table onto the 'master 
> machine' then,
> 
> presuming each row on each copy of the table to have a unique primary 
> key based on the entry values;
> and at  'merge time',
> b) update seriatim from all the various instances into a necessarily 
> large temporary table number 1; then,
> c) update into temporary table 2, using a 'select distinct' to obtain 
> the unique entries; and finally,
> d) copy the resulting temporary table 2, back over the orginal table, in 
> each case.
> 
> 
> So if table A has rows 'a,b.c' and table B has 'a,b,c,d' and table C has 
> 'a,b,c,e', temp 1 will have 'a,a,a,b,b,b,c,c,c,d,e' and table 2 will end 
> up with 'a,b,c,d,e'...and will be copied over A, B and C.
> 
> Is this the way to do it, or am I missing something really easy, such as 
> a horribly complex 'select....join' statement. Note that the schema of 
> all of these tables is exactly the same.
> 
> (And the answer to this has some relevance to updating/merging different 
> instances of mythtv tables....such as moving a mythfilldatabase update 
> from one machine to another without replacing the entire table.)
> 
> Geoff

You could use the UNION operator:

SELECT * FROM table_a
UNION
SELECT * FROM table_b
UNION
SELECT * FROM table_c

UNION by default will remove duplicates.  If you want to keep dupes, use 
UNION ALL.