Filling in data missing in one table from another table.

[billmudry]

Before I really dig in ----- HAPPY NEW YEAR EVERYONE!

A few of you may remember that I have been working on producing a large wood knowledge base (woodsoftheworld.org). It is totally none profit. I am getting much closer to having enough working that soon I hope to be able to declare it at version 1 stage.

I have a large respect on how powerful SQL can be but I still have difficulties to
get my statements right, undoubtedly syntax errors. Hope someone can help with
something a few may look at as elementary. This year I want to get better at writing successful statements. Some things are hard to find exactly what a person is looking for in books or online.
--------------------------

Back months ago, I was able to get a large list of existing woods (species) from the second largest wood collection in the world, the Tervuren Xylarium in the Netherlands, over 17,000 records. I record all botanical names of wood in the 'species' table. All tables used for the project are in the 'TAXA" database.

With the gracious help from others, I was able to import the refined Tervuren list without producing a huge number of duplicates, the new total being just over 15,000 woody species. That was a huge jump in the success of the project!

In fact, I have since proven that the species brought in were so numerous that the
genus table is now missing 669 new genera (genera is plural) that are in the species file.

For those that perhaps do not know, botanical species names are made of two parts, the genus and the epithet. For instance, White Oak has a botanical name of
'Quercus alba'. The 'Quercus' is the genus name, so even if there are some missing
genera, they can be generated out of the species name. The following script does that plus shows that all missing genera in the sci_genera table have no index values .. to be expected at this stage. So far, I have been able to list all the missing genera with this script:

<code>
SELECT DISTINCT
left(species_name,locate(" ", `species_name`)-1) ,`generaID`
FROM `species`
WHERE `generaID`=0
ORDER BY `genus_name`
LIMIT 0 , 5000
</code>

Now that I can have a list of all NEW species not yet reported in the genus table (sci_genera), I need to copy all of these into the genus table so it will be complete. I will expect that the insertion process will automatically generate the
generaID values.

I have tried different things but without required results. Last night when I tried to use a 'INSERT" style of script, it came disappointingly back gave an error saying the two files were of different lengths (end of effort). Now that leaves me wondering whether the best approach is the use "INSERT", 'UPDATE, 'ALTER'(less likely) some other command, or if there is an APPEND command good to use.

If I was to generalize the needed procedure, I need to copy over to a column in one table missing data that is embodied in another column in another table. ...... So, what is the best approach to do this? The parameters that should be needed include:

'species' - The table where all species are listed.
'species_name" - The column where all species are stored.
'sci_genera' - The table where the genera names are stored.
'genus_name' - The column where the genera names are stored ((and the target to
append the derived genera from the species listing.

Remember .... you cannot equate anything to the genus prime key (generaID) because they are not yet generated for the missing genera. I especially look forward to getting a working SQL solution :-).

(Later, once the new generaID values get generated, I will want to copy them back into the species table as a foreign key --- but lets not risk confusing things. That can wait for another day).

Much thanks. My efforts in creating the TAXA Wood Knowledge Base site would not be possible without the help of the programming forums I use.

Bill Mudry
Mississauga, Ontario Canada

[urtrivedi]

This is very well explained, but I still need the sql script of 2 tables with sample data.

Sample data means, you give say 100 rows in one table and some more rows in source table. So that we can help you build the exact query.

[rch1231]

Hello,

I am not sure exactly what fields you are trying to post to the second file but here is the way i normally do what you are attempting.

Step 1. Figure out the record ID or something unique you can use to select the records needed for your insert.

select table1.ID 
from table1
where IS_In_table2 is null

Step 2. Create a select statement that generates output in the order and format needed for the insert into the new table. i.e. if I want to insert into table2 fields table2.f1, table2.f2, table2.f3, table2.f4, and table2.f5 in that order. In table one the corresponding fields are table1.f3, table1.f5, table1.f7, table1.f9, and table1.f15 then my query from would be:

select table1.f3, table1.f5, table1.f7, table1.f9, table1.f15
from table1

Step 3. Merge the two to get the data you need to insert:

select table1.f3, table1.f5, table1.f7, table1.f9, table1.f15
from table1
where table1.ID in (select table1.ID 
from table1
where IS_In_table2 is null)

Step 4. add the insert statement to the query:

Insert into table2 (f1, f2, f3, f4,f5)
select table1.f3, table1.f5, table1.f7, table1.f9, table1.f15
from table1
where table1.ID in (select table1.ID 
from table1
where IS_In_table2 is null)

I hope this helps.

[billmudry]

Rodney, your suggestions sound good. Just thought I would add a short acknowledgement that I read your post ... before I go try some of it --- so you know you are not ignored :-) .

Bill

Hello,

I am not sure exactly what fields you are trying to post to the second file but here is the way i normally do what you are attempting.

Step 1. Figure out the record ID or something unique you can use to select the records needed for your insert.

select table1.ID 
from table1
where IS_In_table2 is null

Step 2. Create a select statement that generates output in the order and format needed for the insert into the new table. i.e. if I want to insert into table2 fields table2.f1, table2.f2, table2.f3, table2.f4, and table2.f5 in that order. In table one the corresponding fields are table1.f3, table1.f5, table1.f7, table1.f9, and table1.f15 then my query from would be:

select table1.f3, table1.f5, table1.f7, table1.f9, table1.f15
from table1

Step 3. Merge the two to get the data you need to insert:

select table1.f3, table1.f5, table1.f7, table1.f9, table1.f15
from table1
where table1.ID in (select table1.ID 
from table1
where IS_In_table2 is null)

Step 4. add the insert statement to the query:

Insert into table2 (f1, f2, f3, f4,f5)
select table1.f3, table1.f5, table1.f7, table1.f9, table1.f15
from table1
where table1.ID in (select table1.ID 
from table1
where IS_In_table2 is null)

I hope this helps.

[rch1231]

Ok thanks and good luck.
Let me know if I can help and if you get it to work...

[mlesniak]

I'm no great expert in SQL but have you looked at the SELECT INTO statement?
A simple version of this is :-

SELECT Column1, Column2, Column3,
INTO Table2
FROM Table1

Obviously, you would need to add the WHERE at the end of it.

Before I really dig in ----- HAPPY NEW YEAR EVERYONE!

A few of you may remember that I have been working on producing a large wood knowledge base (woodsoftheworld.org). It is totally none profit. I am getting much closer to having enough working that soon I hope to be able to declare it at version 1 stage.

I have a large respect on how powerful SQL can be but I still have difficulties to
get my statements right, undoubtedly syntax errors. Hope someone can help with
something a few may look at as elementary. This year I want to get better at writing successful statements. Some things are hard to find exactly what a person is looking for in books or online.
--------------------------

Back months ago, I was able to get a large list of existing woods (species) from the second largest wood collection in the world, the Tervuren Xylarium in the Netherlands, over 17,000 records. I record all botanical names of wood in the 'species' table. All tables used for the project are in the 'TAXA" database.

With the gracious help from others, I was able to import the refined Tervuren list without producing a huge number of duplicates, the new total being just over 15,000 woody species. That was a huge jump in the success of the project!

In fact, I have since proven that the species brought in were so numerous that the
genus table is now missing 669 new genera (genera is plural) that are in the species file.

For those that perhaps do not know, botanical species names are made of two parts, the genus and the epithet. For instance, White Oak has a botanical name of
'Quercus alba'. The 'Quercus' is the genus name, so even if there are some missing
genera, they can be generated out of the species name. The following script does that plus shows that all missing genera in the sci_genera table have no index values .. to be expected at this stage. So far, I have been able to list all the missing genera with this script:

<code>
SELECT DISTINCT
left(species_name,locate(" ", `species_name`)-1) ,`generaID`
FROM `species`
WHERE `generaID`=0
ORDER BY `genus_name`
LIMIT 0 , 5000
</code>

Now that I can have a list of all NEW species not yet reported in the genus table (sci_genera), I need to copy all of these into the genus table so it will be complete. I will expect that the insertion process will automatically generate the
generaID values.

I have tried different things but without required results. Last night when I tried to use a 'INSERT" style of script, it came disappointingly back gave an error saying the two files were of different lengths (end of effort). Now that leaves me wondering whether the best approach is the use "INSERT", 'UPDATE, 'ALTER'(less likely) some other command, or if there is an APPEND command good to use.

If I was to generalize the needed procedure, I need to copy over to a column in one table missing data that is embodied in another column in another table. ...... So, what is the best approach to do this? The parameters that should be needed include:

'species' - The table where all species are listed.
'species_name" - The column where all species are stored.
'sci_genera' - The table where the genera names are stored.
'genus_name' - The column where the genera names are stored ((and the target to
append the derived genera from the species listing.

Remember .... you cannot equate anything to the genus prime key (generaID) because they are not yet generated for the missing genera. I especially look forward to getting a working SQL solution :-).

(Later, once the new generaID values get generated, I will want to copy them back into the species table as a foreign key --- but lets not risk confusing things. That can wait for another day).

Much thanks. My efforts in creating the TAXA Wood Knowledge Base site would not be possible without the help of the programming forums I use.

Bill Mudry
Mississauga, Ontario Canada

[billmudry]

Rodney (and others), Here is what I came up with. First, the SELECT statement
below run by itself worked just fine, bringing forth a list of the 679 genera names, just what I need to copy and insert into another table, The sci_genera table, column genus_name.

SELECT DISTINCT 
left(species_name,locate(" ", `species_name`)-1) 
FROM `species` 
WHERE `generaID`=0
ORDER BY `genus_name`

The second line takes the species names which are always two part......
[genus] (space) [epithet]
and separates all the genera (genus is singular, genera is plural) in a nice
vertical list. The epithet on the right can be discarded. The 'generaID equals
zero' makes sure the only records kept is the missing ones in sci_genera.genus_name.
As you can appreciate, it would be a lot of work to manually add 679 records
each manually if a script can do it.

The DISTINCT statement makes sure the genus names are not repeated.
Since the script properly generates the genera list of missing data, all I should
have to do is insert/append all of this data into sci_genera.genus_name .... job then done. I added an INSERT statement to try doing that:

INSERT INTO sci_genera.genus_name
(
SELECT DISTINCT 
left(species_name,locate(" ", `species_name`)-1) 
FROM `species` 
WHERE `generaID`=0
ORDER BY `genus_name`
LIMIT 0 , 5000
)

The LIMIT statement is only because I am using PHP that will put a useless,
interfering value automatically if I do not. (By the way, does anyone know how to
turn off that feature in phpMyAdmin?)

I got an unexpected result. No syntax type error was reported. Instead it posted:

#1142 - INSERT command denied to user 'taxa@'(ip confidential)" [host name] for
table 'genus_name"

I phoned GoDaddy where my hosting is and had a long talk with a technician. I was
so shocked to find that they will not allow something as basic as an INSERT INTO
command! I might yet decide I need a different hosting company! Incredible. When
I tried to find on their site how I could open up permissions on 'taxa', the techy
said there was NONE(!). In fact, he would not/could not even tell me the permissions that GoDaddy has set permissions to on 'taxa'! <incredible!>.

So, there are two things I would like to ask.

1) Could you please look at that script and tell me if it is written properly
such that (if it wasn't GoDaddy messing up things) the script should run
properly on a system that allows INSET INTO to run. They said I could run
anything I want by getting a partitioned virtual account from them, but at
$30/month, that is an expense I would hope to prevent.

2) IF not this method, how can I otherwise append this list of missing genera
into sci_genera.genus_name? (UPDATE? What else?) If you could write that
part, I will gladly try it out.

I would rather use the power of SQL than to manually edit-in almost 700 records. LOL

Bill

====================================================================
co

Hello,

I am not sure exactly what fields you are trying to post to the second file but here is the way i normally do what you are attempting.

Step 1. Figure out the record ID or something unique you can use to select the records needed for your insert.

select table1.ID 
from table1
where IS_In_table2 is null

Step 2. Create a select statement that generates output in the order and format needed for the insert into the new table. i.e. if I want to insert into table2 fields table2.f1, table2.f2, table2.f3, table2.f4, and table2.f5 in that order. In table one the corresponding fields are table1.f3, table1.f5, table1.f7, table1.f9, and table1.f15 then my query from would be:

select table1.f3, table1.f5, table1.f7, table1.f9, table1.f15
from table1

Step 3. Merge the two to get the data you need to insert:

select table1.f3, table1.f5, table1.f7, table1.f9, table1.f15
from table1
where table1.ID in (select table1.ID 
from table1
where IS_In_table2 is null)

Step 4. add the insert statement to the query:

Insert into table2 (f1, f2, f3, f4,f5)
select table1.f3, table1.f5, table1.f7, table1.f9, table1.f15
from table1
where table1.ID in (select table1.ID 
from table1
where IS_In_table2 is null)

I hope this helps.

[mlesniak]

If you are not phased by installing MySQL (and optionally PHP) on your own computer, you might like to consider creating a mirror image of the database so that you can manage your own user's in MySQL and set the permissions as required. You can then (hopefully) create scripts from your web-based database to re-create it on your own computer. It's a long slog to prove a point.
Secondly, I'm not quite sure about the restrictions your provider has set on the INSERT INTO permission. Is it only the SELECT INTO clause that is prohibited or is it just that you can't perform any scripted INSERT statements? If it's the latter, then how does data get into the table in the first place? Do you have to use a web form to do that?
Would you (for example) be able to create a script, from your original select statement, that would have one line for each INSERT statement that you need? By this I mean creating a script with a series of

INSERT INTO sci_genera (genus_name) VALUES ('genus_name1');
INSERT INTO sci_genera (genus_name) VALUES ('genus_name2');
INSERT INTO sci_genera (genus_name) VALUES ('genus_name3');

type statements.
Would you be allowed to do that?

[billmudry]

If you are not phased by installing MySQL (and optionally PHP) on your own computer, you might like to consider creating a mirror image of the database so that you can manage your own user's in MySQL and set the permissions as required. You can then (hopefully) create scripts from your web-based database to re-create it on your own computer. It's a long slog to prove a point.

In past years, I have installed XAMMP. I found, however, that the results I got were not as accurate as doing it on my hosting site. Its been a few
years, so I can't recall the things that did not work right. Perhaps I should update to the latest XAMMP. I should possibly consider installing Easy PHP to compare. I have seen in the past how trying to configure Apache, PHP and MySQL can get quite involved if done manually. I would rather not have to. AHH.... I remember one thing. I got some virus on my local machine that just loved targeting XAMMP or other PHP programs. Hopefully that is long gone.

You have a point that I then would be able to check if I wrote a script properly
---- independent of the unreasonable restrictions from GoDaddy. Since some pages
rely on others, it would mean eventually making sure that both the local and
online sites were both kept updated. I have to admit that I really like how
Dreamweaver saves each change so easily so I don't have to ftp changes.

Secondly, I'm not quite sure about the restrictions your provider has set on the INSERT INTO permission. Is it only the SELECT INTO clause that is prohibited or is

As I wrote, when the SELECT statement is run separately, it works like a charm,
separating the left halves of all the species and listing them --- the genera data
that I need. It has to be the INSERT statement ..... and confirmed by the techy I
talked to.

it just that you can't perform any scripted INSERT statements? If it's the latter, then how does data get into the table in the first place? Do you have to use a web form to do that?
Would you (for example) be able to create a script, from your original select statement, that would have one line for each INSERT statement that you need? By this I mean creating a script with a series of

INSERT INTO sci_genera (genus_name) VALUES ('genus_name1');
INSERT INTO sci_genera (genus_name) VALUES ('genus_name2');
INSERT INTO sci_genera (genus_name) VALUES ('genus_name3');

type statements.
Would you be allowed to do that?

Whether or not they would allow that is not the question because the number of records (genus names) - a large 679 -- that I need to add to the genus name column in the genera. If I had to set that up, it would be much slower than just running the SELECT statement in phpMyAdmin to show the required names and then using MySQL-FRONT to manually enter each one manually anyway.

MySQL-FRONT, by the way, is fantastic to use when you have a lot of manual data
manipulation to do. I doubt there is any other program like it. With others you
have to use a form for each record. MySQL-FRONT instead works very much like a
spreadsheet. Each cell is active so you just have to enter a value and hit <ENTER>
or just move over to an other cell ---- and it automatically saves(!) :-). It has
saved me hours and hours of time.

It is a sad shame that the author has dropped developing it. I wish I could have got a registered copy. If someone has a copy that can be shared (or at least a key)
please be in touch.
table.

[urtrivedi]

What I can suggest is.
1) Download database copy from live server.
2) Set up database in your local pc in mysql.
3) Note the last record in your main table where you want to add 670 rows.
4) Now run script locally and insert new records in local database (copy of live).
5) export new records to sql script (simple insert command)
6) run script of new records on live server

[billmudry]

I will work on setting up a better local WAMP environment this week. It will
take time to organize .... along with a complete copy of the present website.
The sight with code, data, scans and photos has got quite big now.

Unless someone else has any other code solutions to propose, should I consider
closing this thread or leave it open in case someone does have a suggestion
later? I could always start a new thread later.

hmmm. Guess it wouldn't hurt to ask if any of you have opinions on XAMMP or
Easy PHP. I would rather use some package that pre-configures Apache, PHP and
MySQL. I am not about to learn the intricate details of compiling and linking
all of these together.

Much thanks,

Bill

What I can suggest is.
1) Download database copy from live server.
2) Set up database in your local pc in mysql.
3) Note the last record in your main table where you want to add 670 rows.
4) Now run script locally and insert new records in local database (copy of live).
5) export new records to sql script (simple insert command)
6) run script of new records on live server

[urtrivedi]

I think you can download only those tables that you want to use in select query. Whole database is not required. So that will be less in size.

Another way is through php, it can be done by just looping through table, comparing then inserting. (I think no server will restrict you doing that).