MySQL - INSERT INTO from a Temporary Table

mysql, mysql-error-1064, sql, sql-insert, temporary

Solution

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;

Problem

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.

Original source