Re: Appending one MySQL file to another without duplicates.

On Mon, May 10, 2010 at 7:03 AM, Bill Mudry 
<<mailto:[email protected]>[email protected]> wrote:
I have seen many examples of just getting rid of 
duplicate records all in the same file.

However, I need to know how to append one MySQL 
table into another table without
adding duplicate records into the original file accepting the data.

I ran the following script that successfully 
appended data from what I called the "tervuren_target"
table into the file that drives the species level 
of my botanical wood tree, named
"species_master".
..................................................................................................................................................................................
INSERT INTO species_master (genera, name, 
species_name, authorities, species_source)

SELECT genera, name, species_name, authorities, 
species_source FROM 'tervuren_target'

WHERE tervuren_target.species_name != species_master.species_name;
..................................................................................................................................................................................

The last line was an attempt to avoid carrying 
over duplicates from the Tervuren file into the species file
but an error said that it did not recognize species_master.species_name.


You didn't used the species_master table in you 
select query, hence the mysql couldn't understand it.
Â
So, I tried putting single
quotes as 'species_master.species_name'. The 
program now ran without errors and appended records
for a total of 17,685 combined records.


If you put it into quotes, the it will used as a 
string, not a reference to a field.
Please correct me if I'm wrong:
You have this tervuren_target and you have to 
copy every record which doesn't exists in the species_master

INSERT INTO species_master (genera, name, 
species_name, authorities, species_source)

SELECT DISTINCT genera, name, species_name, 
authorities, species_source FROM 'tervuren_target'
LEFT JOIN species_master
ON
tervuren_target.species_name = species_master.species_name
WHERE species_master.species_name IS NULL

Basically: we select all of the records from 
tervuren_target, link with each record from 
tervuren_target to species_master through the 
species_name, we select only the records, where 
this link is not exists 
(species_master.species_name IS NULL, so we don't 
have records with this species_name), to be sure, I added a DISTINCT.
Maybe you have to tweak the query a littbe bit, 
because mysql is a little bit tricky, it doesnt 
allow by default to insert into a row, which is 
used in  the same statement as source, but with 
table alias you can workaround that, or you can use a third table.

Tyrael