MacLochlainns Weblog

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 or 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.