I guess the docs aren't totally clear on this as you are not the first person to have problems forming a multiple-table UPDATE statement.
http://dev.mysql.com/doc/mysql/en/UPDATE.html UPDATE dp_populate_vals INNER JOIN dp_populate_tables ON dp_populate_tables.tgt_tbl=dp_populate_vals.tgt_tbl SET dp_populate_vals.tbl_id=dp_populate_tables.tbl_id Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ted Byrne <[EMAIL PROTECTED]> wrote on 10/04/2004 03:45:20 PM: > Greetings, > > I'm trying to update existing records in one table so that value of a > column winds up matching the value of a column in a second table, based on > a match in a second column in each table. > > I'm not sure if I'm taking the wrong approach on this, of if I'm missing > something simple in the SQL syntax (like this being illegal), but I'd > appreciate any feedback on why MySQL is returning the "Unknown table" error > when the table does indeed exist (see output below). I can do this > manually (v. small number of records involved) but for cases where doing it > by hand would be impractical, it would be helpful to be able to do this > without writing a script. > > Thanks, > > Ted > > mysql> UPDATE dp_populate_vals > -> SET dp_populate_vals.tbl_id=dp_populate_tables.tbl_id > -> WHERE dp_populate_tables.tgt_tbl=dp_populate_vals.tgt_tbl; > ERROR 1109: Unknown table 'dp_populate_tables' in where clause > mysql> select * from dp_populate_tables; > +--------+-------------+---------------------+ > | tbl_id | tgt_tbl | last_populated | > +--------+-------------+---------------------+ > | 1 | dp_dbusage | 0000-00-00 00:00:00 | > | 2 | dp_logusage | 0000-00-00 00:00:00 | > +--------+-------------+---------------------+ > 2 rows in set (0.01 sec) > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
