Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.2Mb
PDF (RPM) - 25.7Mb
HTML Download (TGZ) - 6.5Mb
HTML Download (Zip) - 6.6Mb
HTML Download (RPM) - 5.6Mb
Man Pages (TGZ) - 158.5Kb
Man Pages (Zip) - 262.1Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  INSERT ... SELECT Syntax

13.2.5.1 INSERT ... SELECT Syntax

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

With INSERT ... SELECT, you can quickly insert many rows into a table from the result of a SELECT statement, which can select from one or many tables. For example:

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

The following conditions hold for INSERT ... SELECT statements:

  • Specify IGNORE to ignore rows that would cause duplicate-key violations.

  • DELAYED is ignored with INSERT ... SELECT.

  • The target table of the INSERT statement may appear in the FROM clause of the SELECT part of the query. However, you cannot insert into a table and select from the same table in a subquery.

    When selecting from and inserting into the same table, MySQL creates an internal temporary table to hold the rows from the SELECT and then inserts those rows into the target table. However, you cannot use INSERT INTO t ... SELECT ... FROM t when t is a TEMPORARY table, because TEMPORARY tables cannot be referred to twice in the same statement. See Section 8.4.4, “Internal Temporary Table Use in MySQL”, and Section B.5.6.2, “TEMPORARY Table Problems”.

  • AUTO_INCREMENT columns work as usual.

  • To ensure that the binary log can be used to re-create the original tables, MySQL does not permit concurrent inserts for INSERT ... SELECT statements (see Section 8.11.3, “Concurrent Inserts”).

  • To avoid ambiguous column reference problems when the SELECT and the INSERT refer to the same table, provide a unique alias for each table used in the SELECT part, and qualify column names in that part with the appropriate alias.

For INSERT ... SELECT statements, see Section 13.2.5.2, “INSERT ... ON DUPLICATE KEY UPDATE Syntax” for conditions under which the SELECT columns can be referred to in an ON DUPLICATE KEY UPDATE clause.

The order in which a SELECT statement with no ORDER BY clause returns rows is nondeterministic. This means that, when using replication, there is no guarantee that such a SELECT returns rows in the same order on the master and the slave, which can lead to inconsistencies between them. To prevent this from occurring, always write INSERT ... SELECT statements that are to be replicated using an ORDER BY clause that produces the same row order on the master and the slave. See also Section 17.4.1.16, “Replication and LIMIT”.

Due to this issue, beginning with MySQL 5.5.18, INSERT ... SELECT ON DUPLICATE KEY UPDATE and INSERT IGNORE ... SELECT statements are flagged as unsafe for statement-based replication. Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when using MIXED mode. (Bug #11758262, Bug #50439)

See also Section 17.1.2.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.

An INSERT ... SELECT statement affecting partitioned tables using a storage engine such as MyISAM that employs table-level locks locks all partitions of the source and target tables. This does not occur with tables using storage engines such as InnoDB that employ row-level locking. This issue is resolved in MySQL 5.6. For more information, see Section 19.5.4, “Partitioning and Table-Level Locking”.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Sasa Mladenovic on May 12, 2011
Good to know that you can do INSERT ... SELECT... UNION also. For example:

INSERT INTO tbl_temp1 (fld_id)
SELECT tbl_temp2.fld_order_id
UNION
SELECT tbl_temp3.fld_order_id
UNION
...
  Posted by Court Lukens on May 18, 2011
You only want to copy one piece of data from another table into your new entry. In this case my unique user_id generated from auto increment portion of another table.

INSERT INTO user_perms (user_id,user_name,pass_hash,require_dez,locked) VALUES ((select user_id from user_info where email='user@email.com'),'jimbob','EMPTY','1','F');
  Posted by Devin Butts on May 20, 2011
I was receiving the "Column count doesn't match value count at row 1" error using the following code:

drop table if exists tempAttend;
create table tempAttend like Attendance;
insert into tempAttend select * from Attendance inner join Teams on Attendance.TeamLink=Teams.idx where Teams.idx=99;

It turns out that the problem was related to the "inner join". The * in the Select ends up retrieving all the fields from both tables. It therefore must be qualified such as:

insert into tempAttend select Attendance.* from Attendance inner join…

  Posted by Paul Jewell on May 7, 2012
Following on from Court's comment - I found that the following structure didn't work:

insert into tblA(fld1, fld2, fld3) values ((select f1, f2 from tblB, tblC where tblB.id = 2 and tblC.name = "test"), 'Field3 text');

The error message was: ERROR 1136 (21S01): Column count doesn't match value count at row 1

However, the following did work:

insert into tblA(fld1, fld2, fld3) values ((select f1 from tblB where tblB.id = 2), (select f2 from tblCwhere tblC.name = "test"), 'Field3 text');

  Posted by Michael John on November 10, 2014
Despite many years of SQL experience it is only in the past week I have started handing BLObs. Whilst Selecting them (for further processing) was not a problem Inserting was! The documentation I found was quite scant and, sometimes, incorrect. At one stage I was using two different syntaxes – one flor a single BLOb and another for two or more. Eventually I came down to the following two syntaxes that work for one to eight BLObs (the maximum I need to insert at one time). The coding is in Python under Linux.

To open the files:-

Doppler = open("/home/mjh/Documents//DemoData/MJH_Doppler", 'r').read()
CT_Scan = open("/home/mjh/Documents//DemoData/MJH_CT_Scan", 'r').read()

First method:-

sql = "Insert into Results (idResults, idClient, TestDateTime, Doppler, CT_Scan) \
Values (24, 8, '2014-11-07 09:33:30', %s, %s)"

cursor.execute(sql, ([Doppler, CT_Scan]))

Second Method:-

sql = "Insert into Results (idResults, idClient, TestDateTime, Doppler, CT_Scan) \
Values (26, 8, '2014-11-07 09:39:30', %(one)s, %(two)s)"

cursor.execute(sql, {'one' : Doppler, 'two' : CT_Scan})

Sign Up Login You must be logged in to post a comment.