[mythtv-users] Possibly OT: Merging Tables, NO duplicates.
R. G. Newbury
newbury at mandamus.org
Tue Nov 11 02:53:39 UTC 2008
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
> SELECT * FROM table_b
> 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,
More information about the mythtv-users