List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 4 2004 8:05pm
Subject:Re: Unknown table ... in where clause
View as plain text  
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
> 

Thread
Unknown table ... in where clauseTed Byrne4 Oct
  • Re: Unknown table ... in where clauseMartijn Tonies4 Oct
  • Re: Unknown table ... in where clauseSGreen4 Oct
    • Re: Unknown table ... in where clauseTed Byrne4 Oct