Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.5Mb
PDF (A4) - 35.5Mb
PDF (RPM) - 34.5Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 201.6Kb
Man Pages (Zip) - 306.9Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

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

14.2.5.1 INSERT ... SELECT Syntax

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

With INSERT ... SELECT, you can quickly insert many rows into a table 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 a INSERT ... SELECT statements:

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

  • The target table of the INSERT statement may appear in the FROM clause of the SELECT part of the query. (This was not possible in some older versions of MySQL.) However, you cannot insert into a table and select from the same table in a subquery.

    When selecting from and inserting into a table at the same time, MySQL creates a temporary table to hold the rows from the SELECT and then inserts those rows into the target table. However, it remains true that 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 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.

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

You can explicitly select which partitions or subpartitions (or both) of the source or target table (or both) are to be used with a PARTITION option following the name of the table. When PARTITION is used with the name of the source table in the SELECT portion of the statement, rows are selected only from the partitions or subpartitions named in its partition list. When PARTITION is used with the name of the target table for the INSERT portion of the statement, then it must be possible to insert all rows selected into the partitions or subpartitions named in the partition list following the option, else the INSERT ... SELECT statement fails. For more information and examples, see Section 20.5, “Partition Selection”.

In the values part of ON DUPLICATE KEY UPDATE, you can refer to columns in other tables, as long as you do not use GROUP BY in the SELECT part. One side effect is that you must qualify nonunique column names in the values part.

The order in which rows are returned by a SELECT statement with no ORDER BY clause is not determined. 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; this can lead to inconsistencies between them. To prevent this from occurring, you should always write INSERT ... SELECT statements that are to be replicated as INSERT ... SELECT ... ORDER BY column. The choice of column does not matter as long as the same order for returning the rows is enforced on both the master and the slave. See also Section 18.4.1.17, “Replication and LIMIT”.

Due to this issue, INSERT ... SELECT ON DUPLICATE KEY UPDATE and INSERT IGNORE ... SELECT statements are flagged as unsafe for statement-based replication. With this change, such statements produce a warning in the log when using statement-based mode and are logged using the row-based format when using MIXED mode. (Bug #11758262, Bug #50439)

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

In MySQL 5.7, an INSERT ... SELECT statement that acted on partitioned tables using a storage engine such as MyISAM that employs table-level locks locks all partitions of the target table; however, only those partitions that are actually read from the source table are locked. (This does not occur with tables using storage engines such as InnoDB that employ row-level locking.) See Section 20.6.4, “Partitioning and Locking”, for more information.


User Comments
  Posted by Sebastian Schneider on March 12, 2004
Having the same problem mentioned above (last_insert_id() returns "0"), I found this solution. You can use the following select statement:

SELECT id FROM mytable WHERE id IS NULL;

"id" has to be an auto_increment column to make it work.
It will return the last_insert_id just as last_insert_id() is expected to do.

example:
mysql> INSERT INTO orders (customer_cust_id, orderdatetime, message, taxrate, shippingprice)
-> SELECT '1', NOW(), null, taxrate, shippingprice FROM customer
-> WHERE cust_id='1';
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT orders_id FROM orders WHERE orders_id IS NULL;
+-----------+
| orders_id |
+-----------+
| 29 |
+-----------+
1 row in set (0.00 sec)

  Posted by on March 25, 2004
INSERT ... SELECT can also be used to combine information from related tables into a single table.

Take the following commands:

CREATE TABLE table1 (user VARCHAR(20) PRIMARY KEY, age INT);
CREATE TABLE table2 (user VARCHAR(20) PRIMARY KEY, os VARCHAR(20));

INSERT INTO table1 (user,age) VALUES ('fred',20);
INSERT INTO table1 (user,age) VALUES ('mary',30);
INSERT INTO table2 (user,os) VALUES ('fred','FreeBSD');
INSERT INTO table2 (user,os) VALUES ('mary','Linux');

Now you can combine these into a single table.

CREATE TABLE newtable (user VARCHAR(20) PRIMARY KEY, age INT, os VARCHAR(20));
INSERT newtable (user,age,os) SELECT table1.user,table1.age,table2.os FROM table1,table2 WHERE table1.user=table2.user;

  Posted by Sander Daams on September 8, 2004
If you want to insert a date field you have to do this in the format (YYYY-MM-DD).
If you have a application that is not in this format (DD-MM-YYYY), you can use substring to correct this in your SQL statement.

Example.

INSERT INTO Orders (idContact, Contractno, Description, Startdate ) VALUES ( '2', '2040906', '', CONCAT(SUBSTRING('07-09-2004',7,4),SUBSTRING('07-09-2004',4,2),SUBSTRING('07-09-2004',1,2) )
)
  Posted by Haakon Meland Eriksen on September 28, 2004
Using INSERT INTO ... SELECT you can copy data from one database to another. You need the correct privileges for both.

Enter the source database, database1:
use database1;

Then write to which fields in the destination database you want to copy to, database2:
INSERT INTO database2.table1 (field1,field3,field9)
SELECT table2.field3,table2.field1,table2.field4
FROM table2;

The order of the selected fields and the inserted fields must match, so you enter the correct data. Before doing this, use "describe database2.table1" and "describe database1.table2", to make sure the new fields can hold the same kind of information.

Practical example:
If you have a large number of WWW-links in one content management system, and you don't want to enter them again if you switch to another, this is the way to do it. Cheers!

NOTE: To copy data from one table to another inside just one database, remove the reference to database2 in the above INSERT... SELECT statement, like this:

INSERT INTO table1 (field1,field3,field9)
SELECT table2.field3,table2.field1,table2.field4
FROM table2;

NOTE TO ABOVE NOTE: You can simplify the above INSERT ... SELECT by removing table2 from the SELECT part, like this:

INSERT INTO table1 (field1,field3,field9)
SELECT field3,field1,field4
FROM table2;

The MySQL documentation describes using commands on a single database very well. The inspiration to write this tip was to show that the same commands apply across databases.
  Posted by Amit Jindal on November 4, 2004
This refers to mysql 4.1.6 and later versions for sure.
I wanted to insert some data and refer to other tables for referential integrity.

Schema (only a sample, not complete schema):

CREATE TABLE T_EXCHANGE (
EXCHANGE_ID INTEGER AUTO_INCREMENT,
EXCH_NAME VARCHAR(10),
EXCH_COUNTRY VARCHAR(10),
PRIMARY KEY (EXCHANGE_ID)
) Engine=InnoDB;

CREATE TABLE T_SEC_TYPE (
TYPEID INTEGER AUTO_INCREMENT,
TYPE_NAME VARCHAR(10),
PRIMARY KEY (TYPEID)
) Engine=InnoDB;

CREATE TABLE T_SEC_DETAIL (
SECID INTEGER AUTO_INCREMENT,
SYMBOL VARCHAR(10) NOT NULL,
TYPEID INTEGER REFERENCES T_SEC_TYPE (TYPEID),
COMPANY_NAME VARCHAR(100),
EXCHANGE_ID INTEGER REFERENCES T_EXCHANGE (EXCHANGE_ID),
ACTIVE TINYINT(1),
PRIMARY KEY (SECID)
) Engine=InnoDB;

Sample data:
INSERT INTO T_EXCHANGE VALUES (NULL, 'NASDAQ','US');

INSERT INTO T_SEC_TYPE VALUES (NULL, 'STOCK');
INSERT INTO T_SEC_TYPE VALUES (NULL, 'INDEX');
INSERT INTO T_SEC_TYPE VALUES (NULL, 'FUND');

Now to insert a row in T_SEC_DETAIL, I needed to refer to above two tables.
Here's a groovy insert:

mysql> INSERT INTO T_SEC_DETAIL SELECT NULL, 'MSFT', TT.TYPEID, 'MICROSOFT CP', EI.EXCHANGE_ID, NULL FROM T_SEC_TYPE TT,
T_EXCHANGE EI WHERE TT.TYPE_NAME='STOCK' AND EI.EXCH_NAME='NASDAQ' AND EI.EXCH_COUNTRY='US';

Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM T_SEC_DETAIL;
+-------+--------+--------+--------------+-------------+--------+
| SECID | SYMBOL | TYPEID | COMPANY_NAME | EXCHANGE_ID | ACTIVE |
+-------+--------+--------+--------------+-------------+--------+
| 5 | MSFT | 1 | MICROSOFT CP | 20 | NULL |
+-------+--------+--------+--------------+-------------+--------+
1 row in set (0.00 sec)

HTH
Amit

  Posted by Brian Huisman on November 10, 2004
CAVEAT: For moving of a small number of rows (<5) the INSERT ... SELECT query is *significantly* slower than inserting the rows individually.

However, when dealing with larger numbers of rows (>10), it is also significantly *faster* than inserting individually. It looks like a balance must be sought.

If you plan on executing a large number of these queries, you might want to check how many rows are to be inserted first before deciding how to go about inserting them; either with INSERT ... SELECT or individually.
  Posted by Thierry Coppey on December 29, 2004
If you want to copy a record from a table to another that is similar but not identical, you have to put all your values inside the select. For example:
CREATE TABLE a (name VARCHAR(20), last VARCHAR(20));
CREATE TABLE b (name VARCHAR(20), last VARCHAR(20), age TINYINT(3));
If you want to copy Joe from a to b and add his age (23) do the following
INSERT INTO b SELECT name, last, 23 FROM a WHERE name="Joe";
  Posted by Julio Nobrega on June 29, 2005
Use REPLACE instead of INSERT if the SELECT returns more than one row with the same values and you are trying to insert on a column with a PRIMARY or an UNIQUE key.

I had to extract from a database some information related to particular records (an specific client), but based on a product purchased. So I reconstructed the tables on another database and issued some INSERT SELECT queries.

Got an "ERROR 1062: Duplicate entry". Here's why:

CREATE TABLE clients (id int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`));
INSERT INTO clients VALUES (1);

CREATE TABLE sales (client_id int(10) unsigned NOT NULL default '0',
product_name char(1) NOT NULL default '');
INSERT INTO sales VALUES (1, 'A');

CREATE TABLE clients_2 (id int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`)) # here we will dump the desired client;

INSERT INTO clients_2
SELECT clients.* FROM clients, sales
WHERE clients.id = sales.client_id AND clients.id = '1'; # no problem

SELECT * FROM clients_2;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)

INSERT INTO sales (client_id, product_name) VALUES (1, 'B'); # client 1 bought product B

TRUNCATE TABLE clients_2; # starting clean

INSERT INTO clients_2
SELECT clients.* FROM clients, sales
WHERE clients.id = sales.client_id AND clients.id = '1'; # duplicate key error

ERROR 1062: Duplicate entry '1' for key 1

SELECT is returning more than one row:

SELECT clients.* FROM clients, sales
WHERE clients.id = sales.client_id AND clients.id = '1';

2 rows in set (0.00 sec)

But this works:

REPLACE INTO clients_2
SELECT clients.* FROM clients, sales
WHERE clients.id = sales.client_id AND clients.id = '1';
  Posted by Lefteris on October 31, 2005
Imagine that you need to clone a set of rows (WHERE season_id=2) in your table 'shows' but while cloning, change the season_id=3.

I was afraid that I would need to use a temporary table, but what's above is much more simple. Unfortunately we have to refer to the columns one by one EXCEPT the unique_key, so we can't use '*'

INSERT INTO shows (start_time, day, title, producer, description, page, category_id, season_id)
SELECT start_time, day, title, producer, description, page, category_id, 3 FROM shows WHERE season_id=2
  Posted by Brano Gerzo on November 7, 2005
I need use ON DUPLICATE KEY with LIMIT (ignore duplicate keys). So the query should look like:

INSERT INTO table_2
SELECT * FROM table_1
LIMIT 10
ON DUPLICATE KEY UPDATE table_2.id = table_2.id

I hope, this will help to someone.
  Posted by Peter Speltz on November 21, 2006
The "ON DUPLICATE KEY UPDATE" Clause was added in 4.1. HTH.
  Posted by Vladimir Prieto on June 25, 2007
if you want to update your data table from a another table (let's say a backup) wich are exactly the same, you can do it like this :

INSERT INTO table_old (field1,field2,...fieldn)
SELECT * FROM table_new WHERE (condition)
ON DUPLICATE KEY UPDATE fieldX = VALUES (fieldX)

hope will help somebody...like me

:)
  Posted by Borja Irizar on January 22, 2008
If you need to duplicate a row in same table with unique keys you can do that

1) Obtain the last unique key
2) Sum 1 to last key
3) execute INSERT SELECT ON DUPLICATE KEY

Example
1) $lastids=mysql_fetch_array(mysql_query("SELECT Id_Inmueble FROM inmueble WHERE 1 ORDER BY Id_Inmueble DESC"));
2) $lastid=$lastids["Id_Inmueble"]+1;
3) mysql_query("INSERT INTO inmueble SELECT * FROM inmueble WHERE Id_Inmueble=".$Id." ON DUPLICATE KEY UPDATE Id_Inmueble=".$ultimoid) or die(mysql_error());
  Posted by Brandon Thomas on January 29, 2008
After much frustration I got my INSERT SELECT to work. Below is my sample:
INSERT INTO inventory ('id', 'pub', 'pmid', 'beg_qty', 'ytd_beg_qty', 'ltd_beg_qty') SELECT '1*0108', 'xxxxx', '12345', '10', vi.ytd_beg_qty = '10', vi.ltd_beg_qty + vi.ltd_beg_qty+'10' FROM inventory AS vi WHERE vi.id = '1*1207' ON DUPLICATE KEY UPDATE inventory.pub = 'xxxxx', inventory.pmid = '12345', inventory.beg_qty = '10', inventory.ytd_beg_qty = '10', inventory.ltd_beg_qty = inventory.ltd_beg_qty+'10';

I hope this helps someone as I was tinkering with it for 2 days to finally get it to work.
  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.