別のテーブルのデータをテーブルに追加する(INSERT ... SELECT文)
MySQL で INSERT...SELECT 文を使用すると別のテーブルに格納されているデータを取得しテーブルにまとめて追加することができます。ここでは MySQL にて別のテーブルのデータをテーブルに追加する方法について解説します。
(Last modified: )
別のテーブルのデータを取得してテーブルに追加する
別のテーブルに格納されているデータを取得してテーブルに追加するには次の書式を使用します。
INSERT INTO tbl_name (col_name1, col_name2, ...) SELECT col1, col2, ... FROM other_table WHERE ...
データを取得したい別のテーブルからどのようにデータを取得するのかを SELECT 文で記述し、データを追加するテーブルでどのカラムに値を格納するのかを指定します。 SELECT 文で指定するカラムの数と INSERT 文で指定するカラムの数は一致していなければいけません。
-- --
では実際に試してみます。データを追加する側として次のようなテーブルを作成しました。データをいくつか追加しておきます。
create table sales (id int auto_increment, name varchar(10), count int, index(id));
insert into sales (name, count) values('Orange', 5);
insert into sales (name, count) values('Apple', 3);
insert into sales (name, count) values('Lemon', 4);
次にデータを参照するテーブルとして次のようなテーブルを作成しました。データをいくつか追加しておきます。
create table olddata (name varchar(10), count int, staff varchar(10));
insert into olddata values('Grape', 6, 'Suzuki');
insert into olddata values('Orange', 3, 'Endou');
insert into olddata values('Melon', 5, 'Suzuki');
それでは olddata テーブルからデータを取得して sales テーブルに追加します。次のように実行しました。
insert into sales (name, count) select name, count from olddata;
データの追加が完了しました。確認のために sales テーブルからデータを取得してみます。
select * from sales;
別のテーブルから取得したデータをテーブルに追加できていることが確認できました。
重複した値を追加しようとしてエラーが出た場合にエラーではなく無視する
データを追加するテーブルのカラムで UNIQUE 制約が設定されているカラムがある場合、別のテーブルから取得するデータの中に既存のデータと重複したデータが含まれているとエラーが発生します。
例えばデータを追加する側として次のようなテーブルを作成した場合で確認してみます。データをいくつか追加しておきます。
create table sales (name varchar(10) unique, count int);
insert into sales values('Orange', 5);
insert into sales values('Apple', 3);
insert into sales values('Lemon', 4);
次にデータを参照するテーブルとして次のようなテーブルを作成しました。データをいくつか追加しておきます。
create table olddata (name varchar(10), count int, staff varchar(10));
insert into olddata values('Grape', 6, 'Suzuki');
insert into olddata values('Orange', 3, 'Endou');
insert into olddata values('Melon', 5, 'Suzuki');
olddata テーブルからデータを取得して sales テーブルに追加しようとすると、 UNIQUE 制約が設定されている name カラムで既にテーブルに格納されている値と同じ値が参照するテーブルに存在するため Duplicate entry '値' for key 'カラム名' というエラーとなります。
insert into sales (name, count) select name, count from olddata;
今回データを追加するために参照したテーブルには 3 つのデータが格納されており、エラーが発生するのはその中の一つですが、 INSERT 文を実行したときにエラーが発生した場合はすべてのデータが追加されません。
実際に sales テーブルのデータを取得してみると、一つもデータが追加されていないことが確認できます。
今回のように重複した値を追加しようとしたエラーが発生した場合に、エラーではなく何もしないように変更するには INSERT 文の次に IGNORE を指定してください。
INSERT IGNORE INTO tbl_name (col_name1, col_name2, ...)
SELECT col1, col2, ... FROM other_table WHERE ...
ではもう一度 olddata テーブルからデータを取得して sales テーブルに追加してみます。今回は IGNORE を指定して次のように実行しました。
insert ignore into sales (name, count) select name, count from olddata;
今回はエラーが発生しませんでした。では sales テーブルからデータを取得してみます。
データを追加するために参照したテーブルには 3 つのデータが格納されていましたが、重複エラーとなるデータを除いた 2 つのデータが追加されていることが確認できました。
重複した値を追加しようとしてエラーが出た場合に追加ではなく更新を行う
他のテーブルからデータを参照してテーブルにデータを追加するときに、重複した値を追加しようとしてエラーになった場合、データを追加するのではなく既存のデータの該当カラムの値を指定した方法で更新するように設定することができます。
次の書式を使用します。
INSERT INTO tbl_name (col_name1, col_name2, ...)
SELECT col1, col2, ... FROM other_table WHERE ...
ON DUPLICATE KEY UPDATE col_name = {expr | DEFAULT}
データを追加したときに重複した値を格納しようとしたエラーとなったデータについては、追加するのではなく既存のデータに更新を行います。
では実際に試してみます。データを追加する側として次のようなテーブルを作成した場合で確認してみます。データをいくつか追加しておきます。
create table sales (name varchar(10) unique, count int);
insert into sales values('Orange', 5);
insert into sales values('Apple', 3);
insert into sales values('Lemon', 4);
次にデータを参照するテーブルとして次のようなテーブルを作成しました。データをいくつか追加しておきます。
create table olddata (name varchar(10), salescount int);
insert into olddata values('Grape', 6);
insert into olddata values('Orange', 3);
insert into olddata values('Melon', 5);
olddata テーブルからすべてのデータを参照して sales テーブルに追加すると、重複した値が一つ含まれているのでエラーとなります。
insert into sales (name, count) select name, salescount from olddata;
今回のように重複した値を追加しようとしたエラーが発生するデータについて、追加ではなく更新するように変更するには次のように実行します。
insert into sales (name, count) select name, salescount from olddata on duplicate key update count=count+values(count);
sales テーブルの重複した値が格納されているデータに対して、count カラムの値として現在 sales テーブルの count カラムの値に格納されている値に、今回追加しようとしたデータの count カラムの値を加算した値で更新します。 VALUES 関数は引数に指定したカラムに追加しようとしたデータの値を取得します。
今回はエラーが発生しませんでした。では sales テーブルからデータを取得してみます。
データを追加するために参照したテーブルには 3 つのデータが格納されていましたが、重複エラーとなるデータを除いた 2 つのデータが追加されており、重複したデータについては追加されるのではなく既存のデータの count カラムの値に、追加しようとしたデータの count カラムの値を加算した値で更新されています。
-- --
INSERT ... SELECT 文を使い、別のテーブルに格納されているデータを取得しテーブルにまとめて追加する方法について解説しました。
( Written by Tatsuo Ikura )
著者 / TATSUO IKURA
これから IT 関連の知識を学ばれる方を対象に、色々な言語でのプログラミング方法や関連する技術、開発環境構築などに関する解説サイトを運営しています。