INSERT Using the WITH Clause in MySQL

As of MySQL v8, we have Common Table Expressions (CTEs) or the WITH clause available. I recently learned how to use the WITH clause in an INSERT statement, similar to INSERT with SELECT. Like always, I enjoy sharing what I learn so let’s all learn together…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

For the sake of simplicity (and my sanity) I’m using this arbitrary sample data:

SELECT *
FROM auto_test;

We can use the WITH clause to generate a name value for the INSERT:

WITH rogue_name AS(
SELECT 'Dillion' AS some_name
)
SELECT * FROM rogue_name;

βœ”οΈ MySQL does not require a table named in the FROM clause of a SELECT statement as some SQL dialects do.


You must be mindful of where the WITH clause is placed in the overall statement.

WITH rogue_name AS(
SELECT 'Dillion' AS some_name
)
INSERT INTO auto_test
SELECT some_name FROM rogue_name;

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 auto_test SELECT some_name FROM rogue_name' at line 4

You can see that an error has been returned on this attempt to INSERT using WITH. At this point, the WITH clause is expecting the next statement to be a SELECT, and that’s just not the case in this query.

However, moving the WITH clause in between the INSERT INTO clause and the SELECT statement executes with no errors:

INSERT INTO auto_test (name)
WITH rogue_name AS(
SELECT 'Dillion' AS some_name
)
SELECT some_name FROM rogue_name;
SELECT *
FROM auto_test;

It’s pretty handy if you have a WITH clause that generates some data you need to INSERT into another table. Let me know if you use the WITH clause for INSERTs too!

Thank you for reading this post. Please share it with someone else who would enjoy it as well.




Josh Otwell has a passion to grow as a PHP Developer, SQL expert, and technical blogger/writer.

Disclaimer: The majority of examples in this post, are performed in a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Like always, just because you can do something doesn’t mean you should. My opinions are my own.

More ways I can help

Disclosure: Some of the services and product links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.


πŸ“° Get your brand, product, or service the attention it needs with affordable classified ad placement in the OpenLampTech newsletter. Thank you for your support!


Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.