MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5.1 INSERT ... SELECT 構文

8.0 English
5.7 English
5.6 English
5.5 English

MySQL 5.6 リファレンスマニュアル / ... / INSERT ... SELECT 構文

13.2.5.1 INSERT ... SELECT 構文

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

INSERT ... SELECT を使用すると、1 つまたは多数のテーブルから多数の行をテーブルにすばやく挿入できます。例:

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

INSERT ... SELECT ステートメントには、次の条件が適用されます。

重複キー違反の原因になる行を無視するには、IGNORE を指定します。
DELAYED は、INSERT ... SELECT では無視されます。
INSERT ステートメントのターゲットテーブルが、クエリーの SELECT 部分の FROM 句に現れてもかまいません。(これは、一部の古いバージョンの MySQL では不可能でした。)ただし、テーブルに挿入し、さらにサブクエリーで同じテーブルから選択することはできません。

テーブルからの選択とそのテーブルへの挿入を同時に行う場合、MySQL は SELECT からの行を保持するための一時テーブルを作成してから、それらの行をターゲットテーブルに挿入します。ただし、TEMPORARY テーブルを同じステートメント内で 2 回参照することはできないため、t が TEMPORARY テーブルのときに INSERT INTO t ... SELECT ... FROM t を使用できない点は引き続き残ります (セクションB.5.7.2「TEMPORARY テーブルに関する問題」を参照してください)。
AUTO_INCREMENT カラムは、通常どおりに機能します。
バイナリログを使用して元のテーブルを確実に再作成できるようにするために、MySQL では、INSERT ... SELECT ステートメントでの並列挿入が許可されません。
SELECT と INSERT が同じテーブルを参照している場合のあいまいなカラム参照の問題を回避するには、SELECT 部分で使用されている各テーブルの一意のエイリアスを指定し、その部分にあるカラム名を適切なエイリアスで修飾します。

MySQL 5.6.2 からは、テーブルの名前に続く PARTITION オプションでソースまたはターゲットテーブル (またはその両方) のどのパーティションまたはサブパーティション (またはその両方) を使用するかを明示的に選択できます。PARTITION がこのステートメントの SELECT 部分にあるソーステーブルの名前とともに使用されている場合は、そのパーティションリストで指定されているパーティションまたはサブパーティションの行のみが選択されます。PARTITION がこのステートメントの INSERT 部分のターゲットテーブルの名前とともに使用されている場合は、選択されたすべての行を、このオプションに続くパーティションリストで指定されているパーティションまたはサブパーティションに挿入できる必要があります。そうでない場合、INSERT ... SELECT ステートメントは失敗します。詳細および例については、セクション19.5「パーティション選択」を参照してください。

ON DUPLICATE KEY UPDATE の値の部分では、SELECT 部分で GROUP BY を使用していないかぎり、ほかのテーブル内のカラムを参照できます。1 つの副作用として、値の部分にある一意でないカラム名を修飾しなければならない点があります。

ORDER BY 句のない SELECT ステートメントが行を返す順序は特定されていません。つまり、レプリケーションを使用している場合、このような SELECT がマスターとスレーブ上で行を同じ順序で返す保証はありません。これにより、マスターとスレーブの間で不整合が発生する場合があります。これが発生しないようにするために、レプリケートされる INSERT ... SELECT ステートメントは常に INSERT ... SELECT ... ORDER BY column として記述するようにしてください。column の選択は、マスターとスレーブの両方で間違いなく行が同じ順序で返されるかぎり問題にはなりません。セクション17.4.1.16「レプリケーションと LIMIT」も参照してください。

この問題のために、MySQL 5.6.4 から、INSERT ... SELECT ON DUPLICATE KEY UPDATE および INSERT IGNORE ... SELECT ステートメントには、ステートメントベースのレプリケーションには安全でないというフラグが付けられます。この変更により、このようなステートメントは、ステートメントベースモードを使用しているときはログ内に警告を生成し、MIXED モードを使用しているときは行ベース形式を使用してログに記録されます。(Bug #11758262、Bug #50439)

セクション17.1.2.1「ステートメントベースおよび行ベースレプリケーションのメリットとデメリット」も参照してください。

MySQL 5.6.6 より前は、テーブルレベルのロックを採用した MyISAM などのストレージエンジンを使用しているパーティション化されたテーブルに対して機能した INSERT ... SELECT ステートメントによって、ソースおよびターゲットテーブルのすべてのパーティションがロックされました。(これは、行レベルロックを採用した InnoDB などのストレージエンジンを使用しているテーブルでは発生しておらず、現在も発生しません。)MySQL 5.6.6 以降では、ターゲットテーブルのすべてのパーティションがロックされますが、ソーステーブルは実際に読み取られたパーティションのみがロックされます。詳細は、セクション19.6.4「パーティショニングとロック」を参照してください。

PREV HOME UP NEXT

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})