Re: Appending one MySQL file to another without duplicates.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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

[Index of Archives]     [PHP Home]     [PHP Users]     [PHP Database Programming]     [PHP Install]     [Kernel Newbies]     [Yosemite Forum]     [PHP Books]

  Powered by Linux