1. ホーム
  2. sql

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

2022-03-16 01:35:57

質問

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')
          );