SQLite-UPSERT *не * вставить или заменить

вставить или заменить это не эквивалент "UPSERT".

скажем, у меня есть сотрудник таблицы с полями id, name и role:

INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO")
INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey")

бум, вы потеряли имя сотрудника номер 1. SQLite заменил его значением по умолчанию.

ожидаемый результат UPSERT будет заключаться в изменении роли и сохранении имени.

ответ Эрика Б это нормально, если вы хотите сохранить только один или может быть два столбца из существующей строки. Если вы хотите сохранить много столбцов, это становится слишком громоздким быстро.

вот подход, который будет хорошо масштабироваться для любого количества столбцов с обеих сторон. Чтобы проиллюстрировать это, я предположу следующую схему:

 CREATE TABLE page (
     id      INTEGER PRIMARY KEY,
     name    TEXT UNIQUE,
     title   TEXT,
     content TEXT,
     author  INTEGER NOT NULL REFERENCES user (id),
     ts      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );

внимание, в частности, что name является естественным ключом строки -id используется только для внешних ключей, так что смысл это для SQLite, чтобы выбрать значение ID при вставке новой строки. Но при обновлении существующей строки на основе ее name, Я хочу, чтобы он продолжал иметь старое значение ID (очевидно!).

я достигаю истинного UPSERT со следующей конструкцией:

 WITH new (name, title, author) AS ( VALUES('about', 'About this site', 42) )
 INSERT OR REPLACE INTO page (id, name, title, content, author)
 SELECT old.id, new.name, new.title, old.content, new.author
 FROM new LEFT JOIN page AS old ON new.name = old.name;

точная форма этого запроса может немного отличаться. Ключом является использование INSERT SELECT с левым внешним соединением, чтобы соединить существующую строку с новыми значениями.

здесь, если строка ранее не существовала, old.id будет NULL и SQLite затем назначит идентификатор автоматически, но если уже была такая строка,old.id будет иметь фактическое значение, и это будет использоваться повторно. Который является точно, что я хотел.

на самом деле это очень гибкий. Обратите внимание, как ts столбец полностью отсутствует со всех сторон-потому что он имеет DEFAULT значение, SQLite будет просто делать правильные вещи в любом случае, так что я не должен заботиться об этом сам.

вы также можете включить столбец оба new и old сторон, а затем использовать, например,COALESCE(new.content, old.content) во внешнем SELECT чтобы сказать "вставьте новый контент, если он был, в противном случае сохраните старый контент" – например, если вы используете фиксированный запрос и связываете новые значения с заполнителями.

Если вы вообще делаете обновления я бы ..

1. начать транзакцию
2. сделать обновление
3. проверяем количество строк
4. Если это 0 сделать вставку
5. Commit

Если вы, как правило, делает вставки, мне бы

1. начать транзакцию
2. попробуйте вставить
3. Проверьте ошибку нарушения первичного ключа
4. если мы получили сообщение об ошибке, выполните обновление
5. Commit

таким образом, вы избегаете выбора, и вы транзакционно звучите на Sqlite.

Я понимаю, что это старый поток, но я работал в sqlite3 в последнее время и придумал этот метод, который лучше подходит для моих потребностей динамического генерирования параметризованных запросов:

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

это все еще 2 запроса с предложением where в обновлении, но, похоже, делает трюк. У меня также есть это видение в моей голове, что sqlite может полностью оптимизировать оператор update, если вызов changes() больше нуля. Независимо от того, действительно ли это происходит, это за пределами моего знание, но человек может мечтать, не так ли? ;)

для бонусных очков вы можете добавить эту строку, которая возвращает вам идентификатор строки, будь то новая вставленная строка или существующая строка.

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

2018-05-18 ПРЕССА ОСТАНОВКИ.

поддержка UPSERT в SQLite! синтаксис UPSERT был добавлен в SQLite с версией 3.24.0 (в ожидании)!

UPSERT-это специальное синтаксическое дополнение к INSERT, которое заставляет INSERT вести себя как обновление или no-op, если вставка нарушает ограничение уникальности. UPSERT не является стандартным SQL. UPSERT в SQLite следует синтаксису, установленному PostgreSQL.

Я знаю, что опаздываю на вечеринку, но....

UPDATE employee SET role = 'code_monkey', name='fred' WHERE id = 1;
INSERT OR IGNORE INTO employee(id, role, name) values (1, 'code monkey', 'fred');

поэтому он пытается обновить, если запись есть, то вставка не является действием-ed.

кроме того:

еще один совершенно другой способ сделать это: в моем приложении я установил свой в памяти rowID, чтобы быть длинным.Maxvalue когда я создаю строку в памяти. (MaxValue никогда не будет использоваться в качестве идентификатора, который вы не проживете достаточно долго.... Затем если rowID не является этим значением, то он уже должен быть в базе данных, поэтому требуется обновление, если это MaxValue, то ему нужна вставка. Это полезно только в том случае, если вы можете отслеживать rowIDs в своем приложении.

вот решение, которое действительно является UPSERT (обновление или вставка) вместо вставки или замены (который работает по-разному во многих ситуациях).

это работает так:
1. Попробуйте обновить, если запись с таким же идентификатором.
2. Если обновление не изменило ни одной строки (NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0)), затем вставить запись.

таким образом, либо существующая запись была обновлена, либо будет выполнена вставка.

важная деталь заключается в использовании изменений () SQL функция, чтобы проверить, если инструкция update попал в какие-либо существующие записи и только выполнить инструкцию insert, если он не попал ни в одну запись.

следует отметить, что функция changes() не возвращает изменения, выполненные триггерами более низкого уровня (см. http://sqlite.org/lang_corefunc.html#changes), поэтому обязательно примите это во внимание.

вот SQL...

Проверить обновления:

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

тест вставить:

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

расширения Аристотель вы можете выбрать из фиктивной таблицы "синглтон" (таблица вашего собственного создания с одной строкой). Это позволяет избежать некоторого дублирования.

Я также сохранил пример переносимого через MySQL и SQLite и использовал столбец "date_added" в качестве примера того, как вы можете установить столбец только в первый раз.

 REPLACE INTO page (
   id,
   name,
   title,
   content,
   author,
   date_added)
 SELECT
   old.id,
   "about",
   "About this site",
   old.content,
   42,
   IFNULL(old.date_added,"21/05/2013")
 FROM singleton
 LEFT JOIN page AS old ON old.name = "about";

лучший подход, который я знаю, это сделать обновление, а затем вставить. "Накладные расходы на выбор" необходимы, но это не страшная нагрузка, так как вы ищете на первичном ключе, который является быстрым.

вы должны иметь возможность изменять приведенные ниже инструкции с именами таблиц и полей, чтобы делать то, что вы хотите.

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

Если кто-то хочет прочитать мое решение для SQLite в Кордове, я получил этот общий метод js благодаря ответу @david выше.

function    addOrUpdateRecords(tableName, values, callback) {
get_columnNames(tableName, function (data) {
    var columnNames = data;
    myDb.transaction(function (transaction) {
        var query_update = "";
        var query_insert = "";
        var update_string = "UPDATE " + tableName + " SET ";
        var insert_string = "INSERT INTO " + tableName + " SELECT ";
        myDb.transaction(function (transaction) {
            // Data from the array [[data1, ... datan],[()],[()]...]:
            $.each(values, function (index1, value1) {
                var sel_str = "";
                var upd_str = "";
                var remoteid = "";
                $.each(value1, function (index2, value2) {
                    if (index2 == 0) remoteid = value2;
                    upd_str = upd_str + columnNames[index2] + "='" + value2 + "', ";
                    sel_str = sel_str + "'" + value2 + "', ";
                });
                sel_str = sel_str.substr(0, sel_str.length - 2);
                sel_str = sel_str + " WHERE NOT EXISTS(SELECT changes() AS change FROM "+tableName+" WHERE change <> 0);";
                upd_str = upd_str.substr(0, upd_str.length - 2);
                upd_str = upd_str + " WHERE remoteid = '" + remoteid + "';";                    
                query_update = update_string + upd_str;
                query_insert = insert_string + sel_str;  
                // Start transaction:
                transaction.executeSql(query_update);
                transaction.executeSql(query_insert);                    
            });
        }, function (error) {
            callback("Error: " + error);
        }, function () {
            callback("Success");
        });
    });
});
}

Итак, сначала выберите имена столбцов с помощью этой функции:

function get_columnNames(tableName, callback) {
myDb.transaction(function (transaction) {
    var query_exec = "SELECT name, sql FROM sqlite_master WHERE type='table' AND name ='" + tableName + "'";
    transaction.executeSql(query_exec, [], function (tx, results) {
        var columnParts = results.rows.item(0).sql.replace(/^[^\(]+\(([^\)]+)\)/g, '').split(','); ///// RegEx
        var columnNames = [];
        for (i in columnParts) {
            if (typeof columnParts[i] === 'string')
                columnNames.push(columnParts[i].split(" ")[0]);
        };
        callback(columnNames);
    });
});
}

затем построить транзакции программно.

"значения" - это массив, который вы должны построить раньше, и он представляет строки, которые вы хотите вставить или обновить в таблицу.

"remoteid" - это идентификатор, который я использовал в качестве ссылки, так как я синхронизирую с моего удаленного сервера.

для использования плагина SQLite Cordova, пожалуйста, обратитесь к официальному ссылке

начиная с версии 3.24.0 UPSERT поддерживается SQLite.

С документация:

UPSERT-это специальное синтаксическое дополнение к INSERT, которое заставляет INSERT вести себя как обновление или no-op, если вставка нарушает ограничение уникальности. UPSERT не является стандартным SQL. UPSERT в SQLite следует синтаксису, установленному PostgreSQL. синтаксис UPSERT был добавлен в SQLite с версией 3.24.0 (в ожидании).

UPSERT-это обычный оператор INSERT, за которым следует специальное предложение on CONFLICT

^{изображение: /images/content/418898/d59a3fced398fbcc6b856d6e32b9747e.gif}

вы действительно можете сделать upsert в SQLite, он просто выглядит немного иначе, чем вы привыкли. Это будет выглядеть примерно так:

INSERT INTO table name (column1, column2) 
VALUES ("value12", "value2") WHERE id = 123 
ON CONFLICT DO UPDATE 
SET column1 = "value1", column2 = "value2" WHERE id = 123

Я думаю, что это может быть то, что вы ищете: о предложении конфликта.

Если вы определяете свою таблицу следующим образом:

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    field1 TEXT 
); 

теперь, если вы делаете вставку с идентификатором, который уже существует, SQLite автоматически обновляет вместо вставки.

Hth...

этот метод ремиксы несколько других методов из ответа на этот вопрос и включает в себя использование CTE (общие табличные выражения). Я представлю запрос, а затем объясню, почему я сделал то, что сделал.

Я хотел бы изменить фамилию сотрудника 300 на DAVIS, если есть сотрудник 300. В противном случае, я добавлю нового сотрудника.

имя таблицы: сотрудники Столбцы: id, first_name, last_name

запрос это:

INSERT OR REPLACE INTO employees (employee_id, first_name, last_name)
WITH registered_employees AS ( --CTE for checking if the row exists or not
    SELECT --this is needed to ensure that the null row comes second
        *
    FROM (
        SELECT --an existing row
            *
        FROM
            employees
        WHERE
            employee_id = '300'

        UNION

        SELECT --a dummy row if the original cannot be found
            NULL AS employee_id,
            NULL AS first_name,
            NULL AS last_name
    )
    ORDER BY
        employee_id IS NULL --we want nulls to be last
    LIMIT 1 --we only want one row from this statement
)
SELECT --this is where you provide defaults for what you would like to insert
    registered_employees.employee_id, --if this is null the SQLite default will be used
    COALESCE(registered_employees.first_name, 'SALLY'),
    'DAVIS'
FROM
    registered_employees
;

в основном, я использовал CTE, чтобы уменьшить количество раз, когда оператор select должен использоваться для определения значений по умолчанию. Поскольку это CTE, мы просто выбираем столбцы, которые мы хотим из таблицы, и оператор INSERT использует это.

теперь вы можете решить, какие значения по умолчанию вы хотите использовать, заменив нули, в функции COALESCE с какими значениями должны быть.

после Аристотель Pagaltzis идея COALESCE С ответ Эрика Б, здесь это опция upsert для обновления только нескольких столбцов или вставки полной строки, если она не существует.

в этом случае представьте, что заголовок и содержимое должны быть обновлены, сохраняя другие старые значения при наличии и вставляя предоставленные, когда имя не найдено:

Примечаниеid вынужден быть NULL, когда INSERT как и должно быть Автоинкремент. Если это просто сгенерированный первичный ключ COALESCE также можно использовать (см. комментарий Аристотеля Пагальциса).

WITH new (id, name, title, content, author)
     AS ( VALUES(100, 'about', 'About this site', 'Whatever new content here', 42) )
INSERT OR REPLACE INTO page (id, name, title, content, author)
SELECT
     old.id, COALESCE(old.name, new.name),
     new.title, new.content,
     COALESCE(old.author, new.author)
FROM new LEFT JOIN page AS old ON new.name = old.name;

таким образом, общее правило будет, если вы хотите сохранить старые значения, используйте COALESCE, Если вы хотите обновить значения, используйте new.fieldname

только что прочитав эту тему и был разочарован тем, что это было нелегко просто для этого "UPSERT"ing, я исследовал дальше...

вы можете сделать это непосредственно и легко в SQLITE.

вместо: INSERT INTO

использование: INSERT OR REPLACE INTO

это именно то, что вы хотите это делать!

SELECT COUNT(*) FROM table1 WHERE id = 1;

если COUNT(*) = 0

INSERT INTO table1(col1, col2, cole) VALUES(var1,var2,var3);

то если COUNT(*) > 0

UPDATE table1 SET col1 = var4, col2 = var5, col3 = var6 WHERE id = 1;