Skip navigation links
12.2.5.2 INSERT DELAYED Syntax »
Section Navigation      [Toggle]

12.2.5.1. INSERT ... SELECT Syntax

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_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.

  • 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. (This was not possible in some older versions of MySQL.) In this case, 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.7.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 allow concurrent inserts for INSERT ... SELECT statements.

  • Currently, you cannot insert into a table and select from the same table in a subquery.

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

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.


User Comments

Posted by Sebastian Schneider on March 12 2004 11:36am[Delete] [Edit]

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 [name withheld] on March 25 2004 10:38am[Delete] [Edit]

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 10:21am[Delete] [Edit]

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 6:18am[Delete] [Edit]

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 6:55am[Delete] [Edit]

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 5:29pm[Delete] [Edit]

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 5:41am[Delete] [Edit]

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 7:59pm[Delete] [Edit]

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 [name withheld] on July 27 2005 1:21am[Delete] [Edit]

You *can* insert into the same table you're selecting from in versions 4.0.14 and later.

(The third and sixth bulleted items at the top of this page contradict each other. The third item is correct: You *can* insert into the same table you're selecting from.)

Typically, when you do this you don't want an exact duplicate but want to change a column or two. You can do this with literals in the select clause:

INSERT INTO ORDERITEMS (ORDERNO, PARTNO, QTY)
SELECT '1002', PARTNO, QTY FROM ORDERITEMS
WHERE ORDERNO = 1001;

(This duplicates order 1001 as order 1002.)




Posted by Lefteris on October 31 2005 8:52pm[Delete] [Edit]

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 2:48pm[Delete] [Edit]

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 8:39pm[Delete] [Edit]

The "ON DUPLICATE KEY UPDATE" Clause was added in 4.1. HTH.

Posted by Vladimir Prieto on June 25 2007 7:56pm[Delete] [Edit]

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 7:04pm[Delete] [Edit]

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 6:29pm[Delete] [Edit]

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.

Add your own comment.