Andrew,
Tuesday, May 14, 2002, 12:43:31 AM, you wrote:
AL> I have a database with several tables. I want to update a field in that
AL> table with a field contained in another table. I have a unique key to use
AL> for a WHERE clause.
AL> This is easy to do with various procedural languages (Business Basic, for
AL> one) but I don't find much info in MySQL documentation for doing this, other
AL> than the UPDATE command (which produces an error, see below). I want to open
AL> a file, read a record, retrieve another record based on a key value in the
AL> previously read record, change the values appropriately, and then write the
AL> updated record back out. ?
AL> I have been successful at running a SELECT like this:
AL> connect database;
AL> SELECT A.*,B.Registrant from A INNER JOIN B USING (Cultivar);
AL> This gives me a listing to the screen that looks like it worked. Now I'd
AL> like to update the table A with what comes from table B (for the field called
AL> Registrant, present in both tables).
AL> UPDATE A SET A.Registrant=B.Registrant WHERE A.Cultivar=B.Cultivar;
AL> When I execute this statement, I get an error that it can't find the second
AL> table in the database but I know it is there--I can access, browse, update
AL> records in that table, etc.
AL> It seems like this should be a common thing to want to do but a gentleman
AL> yesterday on the SuSE list said that this may not be possible with MySQL.
MySQL doesn't currently have multi-table updates. You can do SELECT
statement first and then using another language organize an update cicle,
f.e.:
UPDATE A SET A.Registrant='$Registrant' WHERE
A.Cultivar='$Cultivar';
$Registrant and $Cultivar are some values from the result of
the SELECT query.
Or you can use solution that is described for DELETE in the manual:
http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html
AL> If not, can I write the Query results of the SELECT statement out to a new
AL> table and delete the original table?
Yes, you can use CREATE ... SELECT statement.
AL> TIA for any help.
AL> Andrew Lietzow
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
---------------------------------------------------------------------
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
