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 <ted.byrne@stripped> 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/mysql?unsub=1
>
