[解決済み] SQLite - UPSERT *not* INSERT or REPLACE

質問

http://en.wikipedia.org/wiki/Upsert

SQL Server の Insert Update ストアド プロシージャ

SQLiteでこれを行うには、私が思いつかないような巧妙な方法があるのでしょうか？

基本的に、私はレコードが存在する場合、4つの列のうち3つを更新したい。 存在しない場合は、4番目の列にデフォルト（NUL）値でレコードをINSERTしたいと思います。

IDは主キーなので、UPSERTするレコードは1つだけです。

(UPDATEやINSERTが必要かどうかを判断するためのSELECTのオーバーヘッドを明らかに避けようとしています)

提案ですか？

SQLiteのサイトにあるTABLE CREATEのSyntaxは確認できません。 デモを組んでテストしたわけではありませんが、サポートされていないようです。

もしそうなら、私は3つのカラムを持っているので、実際には次のようになります。

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    Blob1 BLOB ON CONFLICT REPLACE, 
    Blob2 BLOB ON CONFLICT REPLACE, 
    Blob3 BLOB 
);

が、最初の2つのblobは衝突を起こさず、IDだけが衝突を起こします。 つまり、Blob1とBlob2は（希望通り）置換されないと仮定します。

SQLiteでデータをバインドする際のUPDATEは完全なトランザクションである、つまり 更新するために送信された各行が必要です。 準備/バインド/ステップ/ファイナライズステートメント リセット関数が使用できるINSERTとは異なります。

ステートメントオブジェクトのライフサイクルは、次のようなものです。

1. sqlite3_prepare_v2() を使用してオブジェクトを作成します。
2. sqlite3_bind_インターフェースを使用して、ホストパラメータに値をバインドします。
3. sqlite3_step() を呼び出して SQL を実行します。
4. sqlite3_reset()を使ってステートメントをリセットし、ステップ2に戻って繰り返します。
5. sqlite3_finalize()を使用して文オブジェクトを破棄します。

UPDATEはINSERTに比べて遅いと思いますが、主キーを使ったSELECTと比べるとどうでしょうか？

おそらく、select を使用して 4 列目 (Blob3) を読み取り、REPLACE を使用して元の 4 列目と最初の 3 列の新しいデータをブレンドした新しいレコードを書き込むべきでしょうか。

どのように解決するのですか？

テーブルの中に3つのカラムがあると仮定します。ID、NAME、ROLE

BADです。 これにより、ID=1 のすべての列が新しい値で挿入または置換されます。

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BADです。 これは2つのカラムを挿入または置換します。NAMEカラムはNULLまたはデフォルト値に設定されます。

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

GOOD : SQLiteのOn conflict句を使用する SQLiteでUPSERTをサポート! バージョン3.24.0からSQLiteにUPSERT構文が追加されました!

UPSERTはINSERTに追加された特別な構文で、INSERTが一意性制約に違反する場合、UPDATEまたはno-opとして動作するようにします。UPSERTは標準SQLではありません。SQLiteのUPSERTは、PostgreSQLで確立された構文に従います。

GOODだが面倒くさい。 これでカラムのうち2つが更新されます。 ID=1が存在する場合、NAMEは影響を受けません。 ID=1が存在しない場合、NAMEはデフォルト(NULL)になります。

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

これでカラムのうち2つが更新されます。 ID=1が存在する場合、ROLEは影響を受けません。 ID=1が存在しない場合、roleはデフォルト値ではなく「Benchwarmer」に設定されます。

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );