Dear MySQL wizards, Thanks for your help in the past. I'm gaining on it!
Here is a SELECT statement that does exactly what I want it to do... ---------------- SELECT lml.*, ahs.registrant FROM lml INNER JOIN ahs USING (cultivar) OR for a more generic presentation, SELECT a.*, b.field7 FROM a INNER JOIN b USING (field1) ------ And what do I want it to do? Select all fields from a, and one field from b (field7) for just those records that match the key field1. Don't display the orginal value for field7 of A but replace that value with what is in B. This appears to work. Let me state this another way. I have one database with two tables, A and B. There is one identical key field (field1=cultivar) in both databases. The number of records is not the same but I do have a unique key that is the same in both tables. Database A has a field (field7=registrant) that is out of date and I want to UPDATE that data into A. As I understand it, I can't use an update statement on two tables simultaneously. Remember, Table B has the correct data and I simply want to create a new table, or UPDATE that data into A. SO..... I run the query, the system displays the correct data but I need to write this out to a new table. When I try... "#mysql -u mysql database_name -p < file_with_functioning_select statement" ...I simply get a help page for mysql that tells me little. How do I create a temporary table with this select statement and then write the temporary table out to disk so that it becomes permanent? Alternatively, I might be able to use a JOIN statement but I can't use this with the mySQLMan, of which I am aware. TIA for any help, Andrew Lietzow The ACL Group, Inc. On Saturday 18 May 2002 11:41 am, mySQL list wrote: > Hi, hope somebody can help me - what I want to do is simple I'm sure, but I > can't work out how to do it: I want to replace a column in a table, > containing text, with a key to another new table which holds the text. > > Example: > > Given a table, 'people', which contains two columns, name, and town, like > this: > > name town > ------ ------ > fred london > jo liverpool > amy chicago > mary chicago > > I create a new table, towns: > > townid town > ------ ---- > 1 london > 2 liverpool > 3 chicago > > So far good. Now I want to add a townid column to 'people', and update to > point to the corresponding row in 'towns'. I add a new column, townid, but > then how do I update the values?? I have tried both of these: > > UPDATE people SET people.townid = towns.townid WHERE > people.town=towns.town; > > and > > UPDATE people INNER JOIN towns ON people.town = towns.town SET > people.townid = towns.townid; > > mysql gives syntax errors in both cases (both work ok with access 2000). > > Is there a way to do this without creating temporary intermediate tables? > > Any help greatly appreciated! > > > > --------------------------------------------------------------------- > 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 -- Andrew Lietzow The ACL Group, Inc. 515-274-0300 v/f 515-710-1955 c --------------------------------------------------------------------- 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
