Re: Appending one MySQL file to another without duplicates.

On Mon, May 10, 2010 at 7:03 AM, Bill Mudry <[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