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

I think:

update dg_populate_vals v
set v.tbl_id = (select t.tbl_id
  from dp_populate_tables t where t.tgt_tlb = v.tgt_table)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com

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