アナログCPU:5108843109

ゲームと音楽とプログラミング(酒と女とロックンロールのノリで)

('ω') < イザユケエンジニャー

ざっくりSQL入門 #7:クエリを書く<INSERT&UPDATE編>

MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。

今回は、INSERTやUPDATEの構文を用いて、レコードの追加や更新を行う方法について。


INSERT・UPDATEとは?

新しくレコードを追加するときは「INSERT」、
既存レコードを更新するときは「UPDATE」を用います。
新しくレコードを追加したいが既存の場合は上書きしたい(もしくは何もしない)、という場合は
INSERTのオプションで対応します。

新しくレコードを追加する(INSERT)

追加するレコードの指定方法は大きく分けて2つあり、
以下のいずれでも追加することができます。

INSERT ... VALUES ...

「nameが○○、birthdayが△△、genderが…」と、追加するレコードが固定値で分かっている場合。

INSERT INTO 【テーブル名】 (【カラム1】, 【カラム2】, ...)
VALUES                     (【値1】    , 【値2】    , ...)

-- 複数レコードの場合、VALUES以下のカッコをカンマ区切りで記載すればOK
INSERT ... VALUES (...), (...), ...

もちろん、カラムと値の記載順は合わせる必要があります。
また、以下のカラムと値については記載しなくてOKです。

  • NULLが許可されているカラムで、NULLにしておきたい場合
    • NULL不許可で無指定の場合、数値なら0、文字列なら空文字で記録されます
      • INSERTは実行されますが、敢えて指定しないのは推奨しません(warningも出ます)
  • デフォルト値やauto_incrementが設定されているカラムで、その値を設定させたい場合
    • 尚、auto_incrementのカラムに固定値を指定した場合、次のレコードは「指定値+1」になる
INSERT ... SELECT ...

「SELECTクエリの結果セットをそのまま追加したい」という場合。

INSERT INTO 【テーブル名】 (【カラム1】, 【カラム2】, ...)
SELECT                      【カラム1】, 【カラム2】, ...
FROM ... WHERE ...

こちらも、INSERTのカラムとSELECTのカラムの記載順は合わせる必要があります。

既存のレコードを更新する(UPDATE)

以下のUPDATE構文を用いることで、既存レコードの更新が行えます。

UPDATE 【テーブル名】
SET    【更新対象のカラム】 = 【更新する値】
WHERE  【条件】

-- 複数カラムを更新する場合、SET句をカンマ区切りで記載すればOK
UPDATE ...
SET    `hoge` = 'a', `fuga` = 'b', ...
WHERE  ...

条件に合致するレコードが複数ある場合はそのすべてが更新対象になります。
また、WHERE句を省略すると指定テーブルの全レコードが更新対象になります。
うっかり意図しないレコードを上書きしないよう注意しましょう。

基本はこれだけですが、応用的な使い方を以下に記載します。(クリックで展開/折りたたみ)

INSERTするが、重複する場合は上書きする(もしくは無視する)

※方言差の大きい項目です。繰り返しますが、ここではMySQLについて記載します。

todo::これだけで長文記事が一本出来上がるレベルなので
後程書いてリンクを張っておくこととして、ここでは簡単に。

例えば、プライマリキーでもある「id」と、単なる文字列の「name」を持つテーブル「user」があるとします。

現在以下のデータが入っています。

id name
1 Alex

ここに以下のINSERTをすると、idが重複しているためにエラーになりますね。

INSERT INTO `user` (`id`, `name`)
VALUES (1, 'Anna')

ここで、重複しなければ「id=1, name=Anna」をINSERTしたくて、
idが重複するなら、そのidを持つレコードのnameをAnnaに上書きしたい…という場合。

一旦SELECTするとか、サブクエリを駆使する方法もありますが、
INSERT構文のみで「なければINSERT、あればUPDATE」を実現することもできます。

INSERT INTO `user` (`id`, `name`)
VALUES (1, 'Anna')
ON DUPLICATE KEY UPDATE `name` = 'Anna'

通常のINSERTに「ON DUPLICATE KEY UPDATE 【IDが重複していた場合の変更内容】」を付加した形となります。
上記の例は固定値で上書きという単純な形となっていますが、
以下のような書き方も可能です。

ON DUPLICATE KEY UPDATE
  `name` = '名前'                 -- 固定値で上書き(つまり、VALUES句に書いた値でなくともよい)
 ,`tel` = VALUES(`tel`)           -- 本来INSERTするはずだった値で上書き
 ,`mail_address` = `mail_address` -- 元々の値で上書き(=何もしない)
 ,`count` = `count` + 1           -- インクリメント
 ,`status_before` = `status_now`  -- 他のカラムの値で上書き