SQLite - UPSERT *nicht* INSERT oder REPLACE

Question

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

Stored Proc einfügen aktualisieren auf SQL Server

Gibt es eine clevere Möglichkeit, dies in SQLite zu tun, an die ich noch nicht gedacht habe?

Im Grunde möchte ich drei von vier Spalten aktualisieren, wenn der Datensatz vorhanden ist, Wenn er nicht vorhanden ist, möchte ich den Datensatz mit dem Standardwert (NUL) für die vierte Spalte einfügen.

Die ID ist ein Primärschlüssel, so dass es immer nur einen Datensatz für UPSERT geben wird.

(Ich versuche, den Overhead von SELECT zu vermeiden, um festzustellen, ob ich UPDATE oder INSERT durchführen muss)

Vorschläge?

---

Ich kann die Syntax auf der SQLite-Website für TABLE CREATE nicht bestätigen. Ich habe keine Demo erstellt, um sie zu testen, aber sie scheint nicht unterstützt zu werden.

Wenn es so wäre, hätte ich drei Spalten, so dass es tatsächlich so aussehen würde:

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

aber die ersten beiden Blobs verursachen keinen Konflikt, nur die ID würde Ich nehme also an, dass Blob1 und Blob2 nicht ersetzt werden (wie gewünscht).

---

UPDATEs in SQLite beim Binden von Daten sind eine vollständige Transaktion, d.h. Jede zu aktualisierende gesendete Zeile erfordert: Prepare/Bind/Step/Finalize-Anweisungen im Gegensatz zu INSERT, das die Verwendung der Reset-Funktion erlaubt

Der Lebenslauf eines Anweisungsobjekts sieht in etwa so aus:

1. Erstellen Sie das Objekt mit sqlite3_prepare_v2()
2. Binden Sie Werte an Host-Parameter mit sqlite3_bind_ interfaces.
3. Führen Sie das SQL durch Aufruf von sqlite3_step() aus.
4. Setzen Sie die Anweisung mit sqlite3_reset() zurück, gehen Sie zurück zu Schritt 2 und wiederholen Sie ihn.
5. Zerstören Sie das Statement-Objekt mit sqlite3_finalize().

UPDATE ich schätze, ist langsam im Vergleich zu INSERT, aber wie verhält es sich im Vergleich zu SELECT mit dem Primärschlüssel?

Vielleicht sollte ich mit select die vierte Spalte (Blob3) lesen und dann mit REPLACE einen neuen Datensatz schreiben, der die ursprüngliche vierte Spalte mit den neuen Daten für die ersten drei Spalten vermischt?

Answer 1

Ich erkenne, dass dies ein altes Thema ist, aber ich habe in sqlite3 in letzter Zeit gearbeitet und kam mit dieser Methode, die besser geeignet meine Bedürfnisse der dynamisch generieren parametrisierte Abfragen:

insert or ignore into <table>(<primaryKey>, <column1>, <column2>, ...) values(<primaryKeyValue>, <value1>, <value2>, ...); 
update <table> set <column1>=<value1>, <column2>=<value2>, ... where changes()=0 and <primaryKey>=<primaryKeyValue>; 

Es sind immer noch 2 Abfragen mit einer Where-Klausel für die Aktualisierung, aber es scheint zu funktionieren. Ich habe auch die Vision im Kopf, dass Sqlite die Update-Anweisung komplett wegoptimieren kann, wenn der Aufruf von changes() größer als Null ist. Ob es das tatsächlich tut, entzieht sich meiner Kenntnis, aber ein Mann darf ja träumen ;)

Als Bonus können Sie diese Zeile anfügen, die Ihnen die ID der Zeile zurückgibt, unabhängig davon, ob es sich um eine neu eingefügte Zeile oder eine vorhandene Zeile handelt.

select case changes() WHEN 0 THEN last_insert_rowid() else <primaryKeyValue> end;

Answer 2

Seit der Version 3.24.0 wird UPSERT von SQLite unterstützt.

Desde el Dokumentation :

UPSERT ist ein spezieller Syntaxzusatz zu INSERT, der bewirkt, dass sich INSERT wie ein UPDATE oder ein no-op verhält, wenn INSERT eine Einzigartigkeitsbeschränkung verletzen würde. UPSERT ist kein Standard-SQL. UPSERT in SQLite folgt der von PostgreSQL festgelegten Syntax. Die UPSERT-Syntax wurde in SQLite mit der Version 3.24.0 (in Vorbereitung) hinzugefügt.

Ein UPSERT ist eine normale INSERT-Anweisung, auf die die spezielle ON CONFLICT-Klausel folgt

Bildquelle: https://www.sqlite.org/images/syntax/upsert-clause.gif

---

Exemple :

CREATE TABLE t1(id INT PRIMARY KEY, c TEXT);
INSERT INTO t1(id, c) VALUES (1,'a'), (2, 'b');
SELECT * FROM t1;

INSERT INTO t1(id, c) VALUES (1, 'c');
-- UNIQUE constraint failed: t1.id

INSERT INTO t1(id, c) VALUES (1, 'c')
ON CONFLICT DO NOTHING;

SELECT * FROM t1;

INSERT INTO t1(id, c)
VALUES (1, 'c')
ON CONFLICT(id) DO UPDATE SET c = excluded.c;

SELECT * FROM t1;

db<>fiddle demo

Answer 3

Updates von Bernhardt:

Sie können in SQLite tatsächlich ein Upsert durchführen, es sieht nur etwas anders aus, als Sie es gewohnt sind. Es würde etwa so aussehen:

INSERT INTO table_name (id, column1, column2) 
VALUES ("youruuid", "value12", "value2")
ON CONFLICT(id) DO UPDATE 
SET column1 = "value1", column2 = "value2"

Answer 4

Hier ist eine Lösung, bei der es sich wirklich um ein UPSERT (UPDATE oder INSERT) handelt und nicht um ein INSERT OR REPLACE (das in vielen Situationen anders funktioniert).

Es funktioniert folgendermaßen:
1. Versuchen Sie zu aktualisieren, wenn ein Datensatz mit der gleichen Id existiert.
2. Wenn die Aktualisierung keine Zeilen verändert hat ( NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0) ), dann fügen Sie den Datensatz ein.

Es wird also entweder ein bestehender Datensatz aktualisiert oder eine Einfügung vorgenommen.

Wichtig ist, dass Sie die SQL-Funktion changes() verwenden, um zu prüfen, ob die Aktualisierungsanweisung auf vorhandene Datensätze trifft, und die Einfügeanweisung nur dann ausführen, wenn sie auf keinen Datensatz trifft.

Eine Sache, die zu erwähnen ist, ist, dass die Funktion changes() keine Änderungen zurückgibt, die von Triggern auf niedrigerer Ebene durchgeführt wurden (siehe http://sqlite.org/lang_corefunc.html#changes ), also berücksichtigen Sie das unbedingt.

Hier ist die SQL...

Test-Update:

--Create sample table and records (and drop the table if it already exists)
DROP TABLE IF EXISTS Contact;
CREATE TABLE [Contact] (
  [Id] INTEGER PRIMARY KEY, 
  [Name] TEXT
);
INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');
INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record
UPDATE Contact
SET Name = 'Bob'
WHERE Id = 2;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record
INSERT INTO Contact (Id, Name)
SELECT 2, 'Bob'
WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result
SELECT * FROM Contact;

Testeinsatz:

--Create sample table and records (and drop the table if it already exists)
DROP TABLE IF EXISTS Contact;
CREATE TABLE [Contact] (
  [Id] INTEGER PRIMARY KEY, 
  [Name] TEXT
);
INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');
INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record
UPDATE Contact
SET Name = 'Bob'
WHERE Id = 3;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record
INSERT INTO Contact (Id, Name)
SELECT 3, 'Bob'
WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result
SELECT * FROM Contact;

Answer 5

Die beste Methode, die ich kenne, ist eine Aktualisierung, gefolgt von einer Einfügung. Der "Overhead eines Selects" ist zwar notwendig, aber keine große Belastung, da Sie nach dem Primärschlüssel suchen, der schnell ist.

Sie sollten in der Lage sein, die folgenden Anweisungen mit Ihren Tabellen- und Feldnamen zu ändern, um das zu erreichen, was Sie wollen.

--first, update any matches
UPDATE DESTINATION_TABLE DT
SET
  MY_FIELD1 = (
              SELECT MY_FIELD1
              FROM SOURCE_TABLE ST
              WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY
              )
 ,MY_FIELD2 = (
              SELECT MY_FIELD2
              FROM SOURCE_TABLE ST
              WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY
              )
WHERE EXISTS(
            SELECT ST2.PRIMARY_KEY
            FROM
              SOURCE_TABLE ST2
             ,DESTINATION_TABLE DT2
            WHERE ST2.PRIMARY_KEY = DT2.PRIMARY_KEY
            );

--second, insert any non-matches
INSERT INTO DESTINATION_TABLE(
  MY_FIELD1
 ,MY_FIELD2
)
SELECT
  ST.MY_FIELD1
 ,NULL AS MY_FIELD2  --insert NULL into this field
FROM
  SOURCE_TABLE ST
WHERE NOT EXISTS(
                SELECT DT2.PRIMARY_KEY
                FROM DESTINATION_TABLE DT2
                WHERE DT2.PRIMARY_KEY = ST.PRIMARY_KEY
                );