SQL Anywhere での UPDATE OR INSERT

お仕事で SQLAnywhere ばかりいじってるのですが、少々マイナーな文法が多いのでメモを残しておきます。今回はINSERTしたいんだけど、UPDATEになるのも混じっている場合、というやつです。マスタの更新をまとめてする場合に、追加もあれば更新もある。元ネタのテキストデータはあるんだけど、いちいち分けて更新するの面倒くさい（つべこべ言わずにやれよ）という場合に使えるネタですね。

Firebird では　UPDATE OR INSERT

Firebirdでは、バージョン2.1から UPDATE OR INSERT文が使用できます。MERGE文も使えるので、元ネタがテーブルにあるならMERGE文、そうでないならUPDATE OR INSERT文を使います。MATCHING句でカラムを指定しなければ主キーで判定されます。シンプルかつ必要十分な構文ですね。

UPDATE OR INSERT INTO
   {tablename | viewname} [(<columns>)]
   VALUES (<values>)
   [MATCHING (<columns>)]
   [RETURNING <values> [INTO <variables>]]

<columns>      ::=  colname  [, colname  ...]
<values>       ::=  value    [, value    ...]
<variables>    ::=  :varname [, :varname ...]

MERGE INTO {tablename | viewname} [[AS] alias]
   USING {tablename | viewname | (select_stmt)} [[AS] alias]
   ON condition
   WHEN MATCHED THEN UPDATE SET colname = value [, colname = value ...]
   WHEN NOT MATCHED THEN INSERT [(<columns>)] VALUES (<values>)

<columns>  ::=  colname [, colname ...]
<values>   ::=  value   [, value   ...]

Note: It is allowed to provide only one of the WHEN clauses

SQLAnywhere では　INSERT ... ON EXISTING UPDATE VALUES ...

SQLAnywhere で同じことをするにはどうするんだろうということで調べると、MySQLに似た文法で出来るようです。VALUES句の前に ON EXISTING UPDATE を挿入します。

INSERT [ INTO ] [ owner.]table-name [ ( column-name, ... ) ]
[ ON EXISTING { 
   ERROR 
   | SKIP 
   | UPDATE [ DEFAULTS { ON | OFF } ] 
   } ]
{ DEFAULT VALUES 
   | VALUES row-value-constructor }
[ OPTION( query-hint [, ... ] ) ]

DEFAULTS句をONにすると、UPDATE時に指定されていないカラムをデフォルト値で初期化してくれるとか嬉しいオプションもあります。更新しないでSKIPしろよとかERRORにしろよなども指定できる万能性は素晴らしいですね。

MySQL では ON DUPULICATE KEY UPDATE

MySQLの場合は、SQLAnywhereと似てますね。INSERT ... VALUES ... ON DUPUILCATE KEY UPDATE と文の後ろにつくところが違います。また、UPDATEだった場合の更新値を後ろに全部書かないといけないのが面倒ですね。　

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

まとめ

やっぱりFirebirdはシンプルでいいですね。なんでも出来そうなSQLAnywhereも素晴らしい。MySQLが面倒なのが意外でした。