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