INSERT INTO after WITH statement in MySQL [duplicate]

Question

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

chrisbyte · Accepted Answer · 2025-02-11 22:10:03Z

1

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

answered Feb 11 at 22:10

chrisbyte

1,6333 gold badges17 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

principal-ideal-domain Feb 11 at 22:16

I'm used to T-SQL. There, my order is the right one. Thanks for your help!

Collectives™ on Stack Overflow

INSERT INTO after WITH statement in MySQL [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related