MySQL-从临时表插入 [英] MySQL - INSERT INTO from a Temporary Table

问题描述

这似乎很简单，但我坚持使用一个简单的插入语句.参见以下内容:

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;

在临时表行fr.file_title in ('suburbs')中，实际列表是从其他位置动态拉出的(这是出于演示目的).我收到以下错误消息:

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:

错误代码:1054.字段列表"中的未知列"insert_table.resource_id"

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

现在，我可以在整个temp表上运行一次select，它返回正常，只是在update语句中失败.我正在MySQL工作台上运行它.完全不知道这里发生了什么.

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.

推荐答案

您要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;

这篇关于MySQL-从临时表插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！