Dear MySQL Wizards, I have a database with several tables. I want to update a field in that table with a field contained in another table. I have a unique key to use for a WHERE clause.
This is easy to do with various procedural languages (Business Basic, for one) but I don't find much info in MySQL documentation for doing this, other than the UPDATE command (which produces an error, see below). I want to open a file, read a record, retrieve another record based on a key value in the previously read record, change the values appropriately, and then write the updated record back out. ? I have been successful at running a SELECT like this: connect database; SELECT A.*,B.Registrant from A INNER JOIN B USING (Cultivar); This gives me a listing to the screen that looks like it worked. Now I'd like to update the table A with what comes from table B (for the field called Registrant, present in both tables). UPDATE A SET A.Registrant=B.Registrant WHERE A.Cultivar=B.Cultivar; When I execute this statement, I get an error that it can't find the second table in the database but I know it is there--I can access, browse, update records in that table, etc. It seems like this should be a common thing to want to do but a gentleman yesterday on the SuSE list said that this may not be possible with MySQL. If not, can I write the Query results of the SELECT statement out to a new table and delete the original table? TIA for any help. Andrew Lietzow The ACL Group, Inc. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
