別のテーブルのデータ取得してテーブルに追加する(INSERT ... SELECT文)

テーブルにデータを追加するときに、すでに存在しているテーブルに格納されているデータを取得してテーブルに追加することができます。 INSERT ... SELECT 文を使います。ここでは MariaDB で別のテーブルに格納されているデータを取得しテーブルに追加する方法について解説します。

(Last modified: )

別のテーブルのデータをテーブルに追加する

別のテーブルに格納されているデータを取得してテーブルに追加するには次の書式を使用します。 

INSERT INTO tbl_name (col1,col2,...)
  SELECT col1,col2,... FROM another_tbl_name

データを取得したい別のテーブルからどのようにデータを取得するのかを SELECT 文で記述し、データを追加するテーブルでどのカラムに値を格納するのかを指定します。 SELECT 文で指定するカラムの数と INSERT 文で指定するカラムの数は一致していなければいけません。

それでは実際に試してみます。一つ目のテーブルを次のように作成しデータを 3 つ追加しました。

create table user (id int auto_increment, name varchar(10), age int, index(id));

insert into user (name, age) values('Nishi', 30);
insert into user (name, age) values('Oota', 19);
insert into user (name, age) values('Suzuki', 24);

別のテーブルのデータをテーブルに追加する(1)

user テーブルには 3 つのデータが格納されています。

select * from user;

別のテーブルのデータをテーブルに追加する(2)

次にもう一つのテーブルを次のように作成し、データを 2 つ追加しました。

create table olduser (id int, name varchar(10), address varchar(10), userage int);

insert into olduser values(1, 'Kondo', 'Tokyo', 25);
insert into olduser values(2, 'Horie', 'Osaka', 28);

別のテーブルのデータをテーブルに追加する(3)

それでは olduser テーブルに含まれるデータを取得し user テーブルに追加します。次のように実行してください。

insert into user (name, age) select name, userage from olduser;

別のテーブルのデータをテーブルに追加する(4)

olduser テーブルの格納されているデータの中で name カラムと userage カラムの値を取得し、 user テーブルの name カラムと age カラムの値として user テーブルに追加しました。

あらためて user テーブルに格納されているデータを取得してみます。

別のテーブルのデータをテーブルに追加する(5)

もともと格納されていた 3 つのデータに加えて、 olduser テーブルに格納されていた 2 つのデータが追加されて合計で 5 つのデータが格納されていることが確認できました。

重複した値を追加しようとしてエラーが出た場合にエラーではなく無視する

データを追加するテーブルのカラムに UNIQUE 制約などが設定されている場合、別のテーブルからデータを取得してテーブルに追加したときに UNIQUE 制約が設定されたカラムに重複する値を追加しようとするとエラーが発生します。

実際に試してみます。一つ目のテーブルを次のように作成しデータを 3 つ追加しました。

create table user (name varchar(10) unique, age int);

insert into user values('Nishi', 30);
insert into user values('Oota', 19);
insert into user values('Suzuki', 24);

重複した値を追加しようとしてエラーが出た場合にエラーではなく無視する(1)

name カラムには UNIQUE 制約を設定しているので name カラムには重複した値を格納することはできません。

次にもう一つのテーブルを次のように作成し、データを 2 つ追加しました。

create table olduser (name varchar(10), age int);

insert into olduser values('Nishi', 26);
insert into olduser values('Wada', 32);

重複した値を追加しようとしてエラーが出た場合にエラーではなく無視する(2)

それでは olduser テーブルに含まれるデータを取得し user テーブルに追加します。次のように実行してください。

insert into user (name, age) select name, age from olduser;

重複した値を追加しようとしてエラーが出た場合にエラーではなく無視する(3)

「Duplicate entry 'Nishi' for key 'name'」というエラーが発生しました。 UNIQUE 制約が設定されているカラムに重複した値を追加しようとしたためです。

エラーが発生した場合に無視をする

このように別のテーブルから読み込んだデータをテーブルに追加したときにエラーが発生することがありますが、エラーが発生するとそこで処理が止まってしまいます。もしエラーが発生した場合はそのデータについては何も行わず、残りのデータを追加するには次の書式を使用してください。 

INSERT IGNORE INTO tbl_name (col1,col2,...)
  SELECT col1,col2,... FROM another_tbl_name

INSERT のあとに IGNORE を記述します。

それでは先ほどと同じように olduser テーブルに含まれるデータを取得し user テーブルに追加します。次のように実行してください。

insert ignore into user (name, age) select name, age from olduser;

重複した値を追加しようとしてエラーが出た場合にエラーではなく無視する(4)

今度はエラーが発生しませんでした。 user テーブルの内容を確認してみます。

select * from user;

重複した値を追加しようとしてエラーが出た場合にエラーではなく無視する(5)

olduser テーブルに格納されていた 2 つのデータについて、 UNIQUE 制約のため追加できなかったデータは無視されましたが、それ以外のデータは user テーブルに追加されていることが確認できました。

重複した値を追加しようとしてエラーが出た場合に追加ではなく更新を行う

他のテーブルからデータを参照してテーブルにデータを追加するときに、重複した値を追加しようとしてエラーになった場合、データを追加するのではなく既存のデータの該当カラムの値を指定した方法で更新するように設定することができます。

次の書式を使用します。 

INSERT INTO tbl_name (col1,col2,...)
  SELECT col1,col2,... FROM another_tbl_name
    ON DUPLICATE KEY UPDATE col1=expr

実際に試してみます。次のようなテーブルを作成し、データを 3 つ追加しました。

create table user (name varchar(10) unique, address varchar(10));

insert into user values('Nishi', 'Tokyo');
insert into user values('Oota', 'Kyoto');
insert into user values('Suzuki', 'Fukuoka');

重複した値を追加しようとしてエラーが出た場合に追加ではなく更新を行う(1)

name カラムには UNIQUE 制約を設定しているので name カラムには重複した値を格納することはできません。

現在 user テーブルには次のようなデータが格納されています。

select * from user;

重複した値を追加しようとしてエラーが出た場合に追加ではなく更新を行う(2)

次にもう一つのテーブルを次のように作成し、データを 2 つ追加しました。

create table updateuser (name varchar(10), addr varchar(10));

insert into updateuser values('Oota', 'Nara');
insert into updateuser values('Wada', 'Gifu');

重複した値を追加しようとしてエラーが出た場合に追加ではなく更新を行う(2)

それでは updateuser テーブルに含まれるデータを取得し user テーブルに追加します。今回は name カラムの値が重複していたデータがあった場合は、 address カラムの値を読み込んだデータの値に置き換えるようにします。次のように実行してください。

insert into user (name, address) select name, addr from updateuser on duplicate key update address=addr;

重複した値を追加しようとしてエラーが出た場合にエラーではなく無視する(3)

updatetable テーブルのデータを読み込んで user テーブルに追加しました。読み込んだデータのなかの 1 つは、 UNIQUE 属性が設定されたカラムの値が重複していたため、新しいデータとして追加されるのではなく、同じ値を持つデータの address カラムの値が読み込んでデータの値に更新されました。

確認のためにあらためて user テーブルの値を取得してみます。

select * from user;

重複した値を追加しようとしてエラーが出た場合に追加ではなく更新を行う(4)

重複していたデータは address カラムの値が更新され、重複していなかったデータは新しいデータとして追加されていることが確認できました。

-- --

MariaDB で別のテーブルに格納されているデータを取得しテーブルに追加する方法について解説しました。

( Written by Tatsuo Ikura )

Profile
profile_img

著者 / TATSUO IKURA

プログラミングや開発環境構築の解説サイトを運営しています。

カテゴリ
検索