While working with an error that my students surfaced in the Oracle Database 12c, I blogged about the limit of using a subquery in an Oracle
INSERT statement, and I discovered something when retesting it in MySQL. It was a different limitation. I was also surprised when I didn’t find any mention of it through a Google search, but then I may just not have the right keywords.
The original test case uses a subquery to create a multiple row result set for an
INSERT statement. Unfortunately, I discovered that you can’t always embed a
UNION ALL inside a subquery. At least, you can’t when you call the subquery inside an
INSERT statement. For example, let’s create a
DESTINATION table and then we’ll try to insert records with a query that fabricates a result set.
-- Drop the destination table. DROP TABLE IF EXISTS destination; -- Create the destination table. CREATE TABLE destination ( destination_id int unsigned , destination_name varchar(30));
Now let’s try the subquery:
1 2 3 4
INSERT INTO destination ( SELECT 1,'Sample1' UNION ALL SELECT 2,'Sample2' );
It raises the following error message:
ERROR 1064 (42000): 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 'UNION ALL SELECT 2,'Sample2' )' at line 3
I wasn’t sure if I missed something. It turned out that I did. While you can put a subquery inside an
INSERT statement, you can’t embed it inside a set of parentheses when it contains a
UNION ALL set operator. Other statements work with or without parentheses.
1 2 3 4
INSERT INTO destination SELECT 1,'Sample1' UNION ALL SELECT 2,'Sample2';
If somebody has any more to add, I look forward to reading it.