Insert into table from CTE-CodePudding

I tried to insert data from master_data table into tpl_transferred_debtor table. Since tpl_transferred_debtor has primary key tpl_code, I need to use CTE to process data from master_data before inserting.

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;

it give this error

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

I checked the CTE table tmp_tpl_table by remove the insert into line and it works fine.

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;

If I don't use CTE to process the data, and just write a simple insert into ... select, then it works fine.

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';

what did I do wrong here?

EDIT: The MYSQL database version is 8.0.29-0ubuntu0.20.04.3.

CodePudding user response：

Try this syntax:

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