MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL Insert from Query

with 4 comments

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.

Written by maclochlainn

June 15th, 2014 at 12:19 am

Posted in MySQL,sql

Tagged with

4 Responses to 'MySQL Insert from Query'

Subscribe to comments with RSS or TrackBack to 'MySQL Insert from Query'.

  1. It works, but it is not a subquery, it’s a statement. So, you just have to drop the parenthesis.

    Curiously, without the UNION, the parenthesis are allowed. But useless.

    Federico Razzoli

    15 Jun 14 at 2:54 am

  2. Since this seems to be misleading enough to make you (and maybe others…) think that UNION is not supported, I added a bug report to MariaDB for this.

    https://mariadb.atlassian.net/browse/MDEV-6341

    Federico Razzoli

    15 Jun 14 at 3:09 am

  3. mysql> drop table if exists t1;
    Query OK, 0 rows affected (0.01 sec)
     
    mysql> create table t1( a int, b int) engine=innodb;
    Query OK, 0 rows affected (0.01 sec)
     
    mysql> insert into t1 select 1,2 union all select 2,1;
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
     
    mysql> select * from t1;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 |    2 |
    |    2 |    1 |
    +------+------+
    2 rows in set (0.00 sec)

    sbester

    15 Jun 14 at 4:07 am

  4. Federico, I should have tested without parentheses. Thanks!

    maclochlainn

    15 Jun 14 at 10:44 am

Leave a Reply