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
- Starting a blog? I use WordPress for the Digital Owl’s Prose blog. Let’s both save money on the plans offered. 💸
- Get your brand, product, or service the attention it deserves with affordable classified ad placement in the OpenLampTech newsletter.
- Need hosting for your next web application or WordPress site? I highly recommend Hostinger and use them to host my niche bass fishing site. The service is second to none and they provide free SSL.
- 🔒5 Truths I’ve Come To Realize As a Self-taught Developer
Disclosure: Some of this blog post’s services and product links 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 deserves with affordable classified ad placement in the OpenLampTech newsletter. Thank you for your support!