title: MySQL - INSERT INTO from a Temporary Table
body: <p>This seems stupidly easy but I'm stuck with a simple insert statement.See below:</p>
  
  <pre><code>begin work;
  CREATE TEMPORARY TABLE IF NOT EXISTS insert_table AS 
  (
  select
       r.resource_id
      ,fr.file_repos_id
      ,mv.VALUE
  
  from
           resources r
     join  versions v on v.RESOURCE_ID = r.resource_id
     join  metadata_values mv on mv.resource_id = r.resource_id
     join  file_repository fr on fr.file_repos_id = v.foreign_id
  
  where  
          v.version_status = 'C'
      and r.RESOURCE_TYPE = 4
      and fr.file_title in ('suburbs')
  );
  
  
  insert 
      into 
          metadata_values (elem_id,value,resource_type,resource_id,foreign_id,mtvr_id,mett_id) 
      values
          (62,'test',4,insert_table.resource_id,insert_table.file_repos_id,80,4);
  
  rollback work;
  </code></pre>
  
  <p>In the temp table line <code>fr.file_title in ('suburbs')</code>, the actual list is dynamically pulled from somewhere else (this is for demonstration purposes).  I get the following error message:</p>
  
  <p>Error Code: 1054. Unknown column 'insert_table.resource_id' in 'field list'</p>
  
  <p>Now, I can run a select all over the temp table and it returns fine, its just failing at the update statement.  I'm running this from MySQL workbench. Totally lost as to whats going on here.</p>