Dear MySQL Wizards, I discovered a solution that works to resolve this problem. :-)
Here are the steps I took. -------------- 1. At the O/S, I copied the three MySQL files to a new name, a_copy. 2. I used mySQLman to delete an index and then to recreate it. I did this because the system showed zero records in the file even though I knew there were thousands. I figured that I must need to force it to count them all. 3. I put the following in a script file: <INSERT INTO a_copy SELECT a.f1, a.f2, a.f3, a.f4, a.f5, a.f6, b.f7 FROM a, b WHERE a.f1 = b.f1> 4. Next, #mysql -u mysql -p < script_file 5. And bingo, the need I once had is now resolved. I now have a new table with the updated field. Unfortunately, the syntax that I am using for a REPLACE statement DOES NOT work exactly like the INSERT statement, as the documentation leads me to believe, so it's back to the drawing board on that one. We need more examples in the documentation. Does anyone post these? Should mere mortals like I be providing such examples without any quality control? Hope this routine can save someone else time and frustration... Ciao! Andrew Lietzow The ACL Group, Inc. On Wednesday 22 May 2002 09:17 am, Andrew Lietzow wrote: > 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
