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