Appending one MySQL file to another without duplicates.

--=====================_99207041==.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed

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. 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.

However, it did not take long to realize that what it had done is
interpret 'species_master.species_name' as
a string, not a column! Since none of Tervuren records would have
that string in them, it let all the records to append
including (sigh ...) 3,431 duplicate records!

To clean this up, I realize that if I just use a simple statement
with DISTINCT in it, duplicate records from the original
(and precious) records could get erased instead of the duplicate
record that was appended in from the Tervuren data.
That would be a really bad disaster! I will see what ideas you all
have but I am thinking it might be much safer to do
the append operation over again with a better script that will stop
duplicates from carrying over to the species_master
file in the first case instead of trying to clean up the merged file.
(... After all, that was what I was trying to do anyway).

On structures, here are some facts you will wish to have:

- I first made sure that the size, collation, engine and
field names used (as shown above) were identical in both the
tervuren_target table (the source of data to append) and
the species_master table.

- None of the data in the species_master file should ever be
erased by this append of new data.

- The species_master table has many more fields as the
tervuren_target table but any field for the tervuren_target
data being transferred has exact matching fields in the
species_master table. They are under the same folder.

- The comparison of which records is new is by using the
species_name field as comparison keys in both. If they are
identical in both tables for each new record being
appended, that record should not be allowed in, thereby not allowing
duplicate wood names in. (The meaning of duplicate in
this situation is therefore NOT meant from within a table but
across tables.)

I have no idea if there is a way to use 'Distinct" across tables. How
is my best way to merge in new data from the one file
to expand the records in a master file without letting duplicate data in?

Once the append without duplicates works properly, I then copy it
over as table 'species' which is used as the main source
of data in the species level of the botanical tree.
(http://www.prowebcanada.com/taxa). Expanding the number of woods I can
find and report was the original top level reason for starting this project.

Conversationally:
Once this works for me, it will better than doubled the number of
existing wood species I can report. That will be most impressive
progress for the wood knowledge base I am building. Once I have a
general algorithm working, I have many other lists from which I may
also be able extract valued new data --- far faster than I have been
able to before.

I got kind of close but not quite there. I look forward to your help.
This should be relatively basic to most intermediate and
advanced programmers :-) . I am still a junior programmer in PHP and
MySQL but more things are starting to work for me ;-) .


With thanks any and all help in advance,

Bill Mudry
Mississauga, Ontario


--=====================_99207041==.ALT--

Re: Appending one MySQL file to another without duplicates.

--0016e6dd9820859bde048636cd44
Content-Type: text/plain; charset=UTF-8

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

--0016e6dd9820859bde048636cd44--

Re: Appending one MySQL file to another without duplicates.

Hi

As this is not a PHP question and in particular not Windows' related
PHP question, I'll keep my reply brief.

If you had a unique index defined, duplicates wouldn't be allowed. Then
you would only have to add IGNORE to the INSERT to prevent warnings
being generated.
http://dev.mysql.com/doc/refman/5.1/en/insert.html

If that is not possible, then a join query to identify the non-duplicate
rows.
http://dev.mysql.com/doc/refman/5.1/en/join.html

> 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. 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.
>
> However, it did not take long to realize that what it had done is
> interpret 'species_master.species_name' as
> a string, not a column! Since none of Tervuren records would have
> that string in them, it let all the records to append
> including (sigh ...) 3,431 duplicate records!
>
> To clean this up, I realize that if I just use a simple statement
> with DISTINCT in it, duplicate records from the original
> (and precious) records could get erased instead of the duplicate
> record that was appended in from the Tervuren data.
> That would be a really bad disaster! I will see what ideas you all
> have but I am thinking it might be much safer to do
> the append operation over again with a better script that will stop
> duplicates from carrying over to the species_master
> file in the first case instead of trying to clean up the merged file.
> (... After all, that was what I was trying to do anyway).
>
> On structures, here are some facts you will wish to have:
>
> - I first made sure that the size, collation, engine and
> field names used (as shown above) were identical in both the
> tervuren_target table (the source of data to append) and
> the species_master table.
>
> - None of the data in the species_master file should ever be
> erased by this append of new data.
>
> - The species_master table has many more fields as the
> tervuren_target table but any field for the tervuren_target
> data being transferred has exact matching fields in the
> species_master table. They are under the same folder.
>
> - The comparison of which records is new is by using the
> species_name field as comparison keys in both. If they are
> identical in both tables for each new record being
> appended, that record should not be allowed in, thereby not allowing
> duplicate wood names in. (The meaning of duplicate in
> this situation is therefore NOT meant from within a table but
> across tables.)
>
> I have no idea if there is a way to use 'Distinct" across tables. How
> is my best way to merge in new data from the one file
> to expand the records in a master file without letting duplicate data in?
>
> Once the append without duplicates works properly, I then copy it
> over as table 'species' which is used as the main source
> of data in the species level of the botanical tree.
> (http://www.prowebcanada.com/taxa). Expanding the number of woods I can
> find and report was the original top level reason for starting this project.
>
> Conversationally:
> Once this works for me, it will better than doubled the number of
> existing wood species I can report. That will be most impressive
> progress for the wood knowledge base I am building. Once I have a
> general algorithm working, I have many other lists from which I may
> also be able extract valued new data --- far faster than I have been
> able to before.
>
> I got kind of close but not quite there. I look forward to your help.
> This should be relatively basic to most intermediate and
> advanced programmers :-) . I am still a junior programmer in PHP and
> MySQL but more things are starting to work for me ;-) .
>
>
> With thanks any and all help in advance,
>
> Bill Mudry
> Mississauga, Ontario
>

--
Niel Archer



--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Appending one MySQL file to another without duplicates.

--0015174c1b366a1612048638034c
Content-Type: text/plain; charset=UTF-8

On Mon, May 10, 2010 at 8:03 AM, Niel Archer wrote:

> Hi
>
> As this is not a PHP question and in particular not Windows' related
> PHP question, I'll keep my reply brief.
>
> If you had a unique index defined, duplicates wouldn't be allowed. Then
> you would only have to add IGNORE to the INSERT to prevent warnings
> being generated.
> http://dev.mysql.com/doc/refman/5.1/en/insert.html
>
>
I would suggest INSERT ... ON DUPLICATE UPDATE field = field instead of
IGNORE(because IGNORE can suppress other errors), but you are right about
that the unique constraint.

Tyrael

--0015174c1b366a1612048638034c--

Re: Appending one MySQL file to another without duplicates.

--=====================_152975781==.ALT
Content-Type: text/plain; charset="iso-8859-1"; format=flowed
Content-Transfer-Encoding: quoted-printable



On Mon, May 10, 2010 at 7:03 AM, Bill Mudry=20
<billmudry@rogers.com> wrote:
I have seen many examples of just getting rid of=20
duplicate records all in the same file.

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

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

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

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

The last line was an attempt to avoid carrying=20
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=20
select query, hence the mysql couldn't understand it.
=C2
So, I tried putting single
quotes as 'species_master.species_name'. The=20
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=20
string, not a reference to a field.
Please correct me if I'm wrong:
You have this tervuren_target and you have to=20
copy every record which doesn't exists in the species_master

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

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

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

Tyrael

--=====================_152975781==.ALT--

Re: Appending one MySQL file to another without duplicates.

--=====================_153292775==.ALT
Content-Type: text/plain; charset="iso-8859-1"; format=flowed
Content-Transfer-Encoding: quoted-printable



On Mon, May 10, 2010 at 7:03 AM, Bill Mudry=20
<billmudry@rogers.com> wrote:
I have seen many examples of just getting rid of=20
duplicate records all in the same file.

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

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

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

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

The last line was an attempt to avoid carrying=20
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=20
select query, hence the mysql couldn't understand it.
=C2
So, I tried putting single
quotes as 'species_master.species_name'. The=20
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=20
string, not a reference to a field.
Please correct me if I'm wrong:
You have this tervuren_target and you have to=20
copy every record which doesn't exists in the species_master

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

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

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

Tyrael


--=====================_153292775==.ALT--