MySQL - INSERT INTO from a Temporary Table

Question

This seems stupidly easy but I'm stuck with a simple insert statement.See below:

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;

In the temp table line fr.file_title in ('suburbs'), the actual list is dynamically pulled from somewhere else (this is for demonstration purposes). I get the following error message:

Error Code: 1054. Unknown column 'insert_table.resource_id' in 'field list'

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.

Answer 1

You want insert . . . select:

insert into  metadata_values(elem_id,value, resource_type, resource_id, foreign_id,
                             mtvr_id, mett_id
                            ) 
    select 62, 'test', 4, insert_table.resource_id, insert_table.file_repos_id, 80, 4
    from insert_table;