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

Using insert_table.column_name does not automatically use the table. To do that youo need to either use a variable or a select statement.

I think what you are looking for is the following, though it will insert multiple rows with elem_id 62 if they exist in insert_table. In fact this will insert N rows with the same elem_id, value, mtvr_id, and met_id, where N is number of rows in insert_table.

insert     into 
    metadata_values (elem_id,value,resource_type,resource_id,foreign_id,mtvr_id,mett_id) 
values
    Select    
        62,'test',4,insert_table.resource_id,insert_table.file_repos_id,80,4
    From    insert_table

You can save yourself the memory overhead by skipping the temporary table altogether.

 insert     into 
    metadata_values (elem_id,value,resource_type,resource_id,foreign_id,mtvr_id,mett_id) 
values
     Select    
        62,mv.VALUE,4,r.resource_id,fr.file_repos_id,80,4
    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')

Answer 2

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;