Re: Need help updating a whole column in one table from another table

At 02:37 PM 4/14/2009, you wrote:

(got work to do during business hours) on the differences between UPDATE and INSERT. INSERT creates a new entry, update changes an existing ones. So you cannot use INSERT to corect your data.

Then it must work on a created field that has not been populated yet? It doesn't also create a field, does it?

$sciName is the field for storing all the botanical name of woods in table

Not really. $sciName is a variable in PHP. I was trying to ascertain where/how it gets populated. I'm guessing a read from the table as part of a loop.

Oops. Sorry, That should have been sciName for the name of the field, not $sciName. I got too used to it becoming $sciName later.

If there is only a single space, you could also have done: $name = explode($sciName, ' ');

Argggh! Got the syntax wrong.

(some older lines taken out for brevity)

Given the foillowing: $pieces = explode(' ', 'one two three'); $pieces will be an array containing [0] => 'one' [1] => 'two' [2] => 'three'

Oh! That is interesting. So you can "explode" parts of a string, too, into separate words? That almost suggests a multidimensional array by the time you do this for all species in the database. I had thought that it works only in dividing up a record into an array by its field contents.

the string has been exploded into fragments using the supplied character (in this case a space) as the break point. This is similar to what you were doing, but a single function call.

This would give you an array with the genus in $name[0] and other part in $name[1]

....... so $name[0] would have the current genus name, right?

The original files were made years ago, not recently, so that would be a lot of work to recreate such CREATE statements for a task that should prove to require a simple solution. Would that not be overkill? I meant the SQL statement used to create the database/tables. Something like CREATE TABLE sci_genera (

          generalID int(11) NOT NULL auto_increment,
          genus_name varchar(255) NOT NULL,
          PRIMARY KEY  (id),
          UNIQUE KEY genus (genus)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8

which is how I guess your sci_genera table would be created with just the fields you described. This contains a lot of useful info about your structure without you having to explain it yourself. Assuming the above table structure and the following for species2 CREATE TABLE species2 (

            generalID int(11) NOT NULL,
            species_name varchar(255) NOT NULL default '',
            PRIMARY KEY  (species_name),
            KEY genus_id (genusID)
          ) ENGINE=MyISAM DEFAULT CHARSET=utf8

I used the two examples you originally provided to create two rows in each table sci_genera ID genus_name 1 Quercus 2 Dalbergia species2 ID species_name

0    Quercus rubra
0    Dalbergia nigra

The following SQL query UPDATE species2 AS s LEFT JOIN sci_genera AS sg ON (sg.genus_name = LEFT(s.species_name, LOCATE(' ', s.species_name) - 1)) SET s.generalID = sg.generalID; alters the contents of species2 to become ID species_name 1 Quercus rubra 2 Dalbergia nigra Assuming I understood your intent correctly, this should be the result you are after. This should fix your table. It does what you are trying with PHP, but using SQL only. With indices on the relevant fields it will probably be (much) faster too. -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Windows Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php