[SOLVED] INSERT INTO after WITH statement in MySQL

INSERT INTO after WITH statement in MySQL

I have no idea why I can't use an INSERT INTO combined with an WITH statement:

drop table if exists testdb.with_insert_into;
create table testdb.with_insert_into (id int);

with test_data as
(select 3 id)
insert into testdb.with_insert_into (id)
select * from test_data;

I'm using version 8.0.41 of MySQL.

It outputs the 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 testdb.with_insert_into (id) select * from test_data' at line 3

Solution

You are close. INSERT INTO needs to come first, then the WITH after. It looks and feels weird, but it is the correct order.

This works:

drop table if exists with_insert_into;
create table with_insert_into (id int);

insert into with_insert_into (id)    -- first
with test_data as                    -- second
(select 3 id)
select * from test_data;             -- finally