从CTE插入表格

Question

我试图将数据从master_data表插入到tpl_transferred_debtor表中。由于tpl_transferred_debtor具有主键tpl_code，因此我需要使用CTE来处理来自插入前master_data。

with distinct_object_code_id as (
select MIN(id) as id
from master_data
where original_type = 'tpl'
group by object_code
), 
tmp_tpl_table as (
select 
md.accounting_id as accounting_id, 
md.object_code as object_code,
md.name as name,
md.created_at as created_at,
md.updated_at as updated_at
from master_data md
join distinct_object_code_id using(id)
)
insert into tpl_transferred_debtor (debtor_id, tpl_code, status, description, created_at, updated_at)
select 
tmp.accounting_id, 
tmp.object_code, 
'active', 
tmp.name, 
tmp.created_at, 
tmp.updated_at
from tmp_tpl_table tmp;

它给出这个错误

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into tpl_transferred_debtor (debtor_id, tpl_code, status, description, cr' at line 17

我通过删除insert into行检查了CTE表tmp_tpl_table，它运行良好。

with distinct_object_code_id as (
select MIN(id) as id
from master_data
where original_type = 'tpl'
group by object_code
), 
tmp_tpl_table as (
select 
md.accounting_id as accounting_id, 
md.object_code as object_code,
md.name as name,
md.created_at as created_at,
md.updated_at as updated_at
from master_data md
join distinct_object_code_id using(id)
)
select 
tmp.accounting_id, 
tmp.object_code, 
'active', 
tmp.name, 
tmp.created_at, 
tmp.updated_at
from tmp_tpl_table tmp;

如果我不使用CTE来处理数据，只写一个简单的insert into ... select，那么它工作得很好。

insert into tpl_transferred_debtor (debtor_id, tpl_code, status, description, created_at, updated_at)
select 
tmp.accounting_id, 
tmp.object_code, 
'active', 
tmp.name , 
tmp.created_at, 
tmp.updated_at
from master_data tmp
where tmp.original_type = 'tpl';

我在这里做错了什么？

编辑：MYSQL数据库版本为8.0.29-0ubuntu0.20.04.3。

Answer 1

尝试以下语法：

INSERT INTO tmp( tmp_id )
WITH cte AS (
SELECT 1 AS tmp_id FROM dual
)
SELECT tmp_id
FROM cte;

https://dev.mysql.com/doc/refman/8.0/en/with.html