ユニーク制約などの違反時に指定の値で上書きする

広告

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

次の書式を使います。 

INSERT INTO tbl_name (col_name1, col_name2, ...)
  VALUES (value1, value2, ...)
  ON DUPLICATE KEY UPDATE col_name_x=value_x, col_name_y=value_y, ...;

ユニーク制約などで違反するデータを追加しようとした時に、データを新規に追加するかわりに既存のデータの指定のカラムの値を更新するということです。この時、更新するカラムは複数指定できますが、エラーの元となるユニーク制約などが設定されたカラムの値は更新できません。またエラーが出ずに新規に追加が成功した場合に格納する値と、エラーが発生し既存のデータを更新する場合の値を別々に指定することができます。

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

mysql> create table usertbl(user varchar(20) unique, visit int, browse int);

p5-1

「user」カラムにはユニーク制約が設定してありますので、重複した値を持つことはできません。

ではデータを追加します。次のように入力して下さい。 

mysql> insert into usertbl values('yamada', 1, 5)
    -> on duplicate key update visit=visit+1, browse=browse+5;

p5-2

新規にデータが追加されました。

この場合、エラーが発生しない場合には新規にデータを追加します。またユニーク制約のエラーが発生した場合には「user」カラムの値が'yamada'のデータを更新します。更新はまず「visit」カラムの値を取り出し1を加えて再度格納し、同じように「browse」カラムの値を取り出し5を加えた値を格納するようにしています。

今回更新する値として「visit+1」や「browse+5」という記述をしていますが、値の部分でカラム名を記述すると、対象データの指定したカラムの値を参照することができます。

では「user」カラムの値が既に存在するようなデータを追加します。次のように入力して下さい。 

mysql> insert into usertbl values('yamada', 1, 7)
    -> on duplicate key update visit=visit+1, browse=browse+7;

p5-3

今回はユニーク制約に違反するデータを追加しようとしたため、新規にデータを追加するのではなく「user」カラムの値が'yamada'のデータに対して「visti」カラムと「browse」カラムの値を新しい値で更新しています。

VALUES関数

更新する値を指定する部分でVALUES(col_name)を使用すると、引数のカラムに対して新規でデータが追加する場合に格納するはずの値を参照できます。

例えば次のようなSQL文を例に考えてみます。 

mysql> insert into usertbl values('yamada', 1, 7)
    -> on duplicate key update visit=visit+1, browse=browse+7;

これは次のように記述することもできます。 

mysql> insert into usertbl values('yamada', 1, 7)
    -> on duplicate key update visit=visit+1, browse=browse+values(browse);

エラーが発生しない場合には「browse」カラムに7を格納しようとしていますので、values(browse)は7を返すことになります。

( Written by Tatsuo Ikura )