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

[R. G. Newbury]

George Mari wrote:
> R. G. Newbury wrote:
>> 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.)
>>
> 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.

Answer number 2. Slightly more complex than a straight REPLACE, but, on 
reflection, REPLACE would involve replacing every row in the master 
table every time through one of the slave tables. A Union would require 
more cpu and a lot less disk thrash!

Answer number 3 came off-list, and pointed me in another direction:

Using the IGNORE keyword in the INSERT portion of the statement, as in

insert ignore into tableA select * from tableB;

(This is, in effect, insert into A select * from B where not exists 
(select * from A).

And run this twice, once for tableB and once for tableC

INSERT IGNORE and REPLACE are in a sense opposites of each other in the 
way they act, although both will give the desired result, but one does 
it by keeping the original table and ignoring the duplicates when 
inserting, while the other replaces all the duplicates along the way as 
well as inserting the new.

Dang I love this list!

Thanks to all,

Geoff