SQL Anywhere 行の挿入と更新を1ステートメントで

行の挿入と更新を1ステートメントでできるとトランザクションを考える必要がなく便利な場合が多い。
SQL Anywhereの場合はINSERT INTO ～ ON EXISTING UPDATE という句を使うとプライマリーキーを比較して挿入と更新を自動で切り替えて実施してくれる。


例として、前記事と同じこのようなテーブルに対して

CREATE TABLE mytable (
    pkey      varchar(10) NOT NULL,
    value     varchar(20),
    created   datetime NOT NULL DEFAULT current timestamp,
    modified  datetime NOT NULL DEFAULT timestamp,
    PRIMARY KEY (pkey)
)

１回目の実行結果

INSERT INTO mytable (pkey,value)
ON EXISTING UPDATE
VALUES('1', 'abc');

INSERTとして実行されます。

pkey	value	created	modified
1	abc	2011-11-02 23:44:39.179	2011-11-02 23:44:39.179

２回目の実行結果

INSERT INTO mytable (pkey,value)
ON EXISTING UPDATE
VALUES('1', 'xyz');

プライマリーキーであるpkey=1に対するUPDATEとして実行されます。

pkey	value	created	modified
1	xyz	2011-11-02 23:44:39.179	2011-11-03 00:06:21.069

他のRDBMSではMERGEやREPLACEというのが似たような動きをしてくれるようである。

SQL Anywhere 行の作成日と更新日を自動でつける

CREATE TABLE文のカラムにDEFAULT句をつけると、そのカラムの値が省略された場合のデフォルト値を自動で入れることができる。SQL Anywhereの場合はこのDEFAULT句を使って行の作成日や更新日を自動でつけることができる。

current timestamp

行が挿入された時間

timestamp

行が挿入または更新された時間

例としてこのような定義のテーブルがあった場合・・・

CREATE TABLE mytable (
    pkey      varchar(10) NOT NULL,
    value     varchar(20),
    created   datetime NOT NULL DEFAULT current timestamp,
    modified  datetime NOT NULL DEFAULT timestamp,
    PRIMARY KEY (pkey)
)

行の追加結果

INSERT INTO mytable (pkey,value) VALUES('1', 'abc');

pkey	value	created	modified
1	abc	2011-11-02 23:44:39.179	2011-11-02 23:44:39.179

行の更新結果

UPDATE mytable SET value='xyz' WHERE pkey='1';

pkey	value	created	modified
1	xyz	2011-11-02 23:44:39.179	2011-11-02 23:45:32.928

こんな感じで、modifiedのほうはUPDATE実行時間に変わりますが、createdのほうはINSERT実行時間のまま変わりません。