他のテーブルのデータを追加

広告

他のテーブルのデータを新しいテーブルに追加する方法を確認します。次の書式を使います。 テーブルの中のカラムにプライマリーキー制約やユニーク制約が設定されている場合に、データを追加すると制約違反となる場合があります。このような場合通常はエラーとなりますが、エラーを発生させる代わりに既存のデータを指定の値で上書きすることができます。

次の書式を使います。 

INSERT INTO tbl_name (col_name1, col_name2, ...) SELECT ...;

取得したいデータをSELECT文を使って記述します。このSELECT文で指定するカラム数は、追加するテーブルで値を格納するカラム数に一致していなければなりません。

サンプル

では実際に試してみます。次のようなテーブルを作成しました。 

mysql> create table sales(name varchar(20) unique, count int);

p6-1

また元になるテーブルとして次の2つのテーブルを作成します。 

mysql> create table tokyo_sales(name varchar(20), count int, d date);

mysql> create table osaka_sales(name varchar(20), count int, d date);

p6-2

この2つの元となるテーブルには次のようにデータが格納されています。

p6-3

p6-4

それでは「tokyo_sales」テーブルからデータを取得し「sales」テーブルに追加してみます。次のように入力して下さい。 

mysql> insert into sales select name, count from tokyo_sales;

p6-5

データが追加されました。ではデータを取得して確認してみます。

p6-6

「tokyo_sales」テーブルに含まれていたデータが「sales」テーブルに追加されているのが確認できました。

IGNORE

「tokyo_sales」テーブルと同じように「osaka_sales」テーブルからデータを追加しようとするとエラーが発生します。これはデータを追加する「sales」テーブルの「name」カラムにユニーク制約が設定してあるためで、「osaka_sales」テーブルからデータを追加しようとすると重複が発生するためです。

p6-7

エラーが発生した場合、データの追加は行われません。

このような場合にエラーを発生させないためにはINSERT文に「IGNORE」を指定します。(IGNOREについての詳細は「ユニーク制約などの違反時にもエラーを発生させずに終了」を参照して下さい)。

それでは試してみます。次のように入力して下さい。 

mysql> insert ignore into sales select name, count from osaka_sales;

p6-8

他のテーブルからデータを取得して追加する場合、ユニーク制約に違反しているデータは追加されませんがそれ以外のデータは追加が行われます。よって今回は2つのデータが追加されました。

p6-9

ON DUPLICATE KEY UPDATE

また、ユニーク制約などに違反した場合、エラーとするかわりに既存のデータを更新することもできます。これにはON DUPLICATE KEY UPDATE句を使用します。(ON DUPLICATE KEY UPDATE句についての詳細は「ユニーク制約などの違反時に指定の値で上書きする」を参照して下さい)。

それでは試してみます。実行する前に一度「sales」テーブルのデータを全て削除し、その後で「tokyo_sales」テーブルのデータを追加した状態に戻してあります。

p6-10

続いて次のように入力して「osaka_sales」テーブルのデータを追加して下さい。 

mysql> insert into sales select name, count from osaka_sales
    -> on duplicate key update count=sales.count+values(count);

p6-11

今回のINSERT文では、ユニーク制約などに違反しないデータはそのまま追加します。そしてユニーク制約などに違反するデータについては、「count」カラムに格納されていた値に追加しようとしたデータの「count」カラムの値を加算した値で「count」カラムの値を更新します。

データを取得してみると次のように表示されます。

p6-12

追加しようとしたデータの中で「name」カラムの値が'Orange'のデータはユニーク制約に違反するため追加はできませんので、そのデータの「count」カラムの値を「sales」テーブルに既にあった「name」カラムの値が'Orange'のデータの「count」カラムの値に加算だけを行っています。よって「name」カラムの値が'Orange'のデータの「count」カラムの値は 12＋4＋7＝21 となっています。

( Written by Tatsuo Ikura )