SQLite - UPSERT * не * INSERT или REPLACE

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

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

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

Думаю, это может быть то, что вы ищете: Предложение ON CONFLICT.

Если вы определите свою таблицу так:

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

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

Hth ...

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

1. Начать транзакцию
2. Сделайте обновление
3. Проверить количество строк
4. Если это 0, сделайте вставку
5. Совершить

Если вы обычно делаете вставки, я бы

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

Таким образом, вы избегаете выбора и получаете надежную транзакцию на Sqlite.

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

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

Вместо использования: INSERT INTO

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

Это именно то, что вы хотите!

INSERT OR REPLACE - это НЕТ, эквивалентное «UPSERT».

Скажем, у меня есть таблица Employee с полями 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 - изменение роли и сохранение имени.

Предположим, что в таблице три столбца: ID, NAME, ROLE.

ПЛОХО: Это вставит или заменит все столбцы новыми значениями для ID = 1:

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

ПЛОХО: Это вставит или заменит 2 столбца ... столбец NAME будет установлен в NULL или значение по умолчанию:

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

ХОРОШО: использовать SQLite при конфликте Поддержка UPSERT в SQLite! Синтаксис UPSERT был добавлен в SQLite с версией 3.24.0!

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

ХОРОШО, но энергично: Это обновит 2 столбца. Когда ID = 1 существует, ИМЯ не изменится. Когда ID = 1 не существует, будет использоваться имя по умолчанию (NULL).

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

Это обновит 2 столбца. Когда существует ID = 1, РОЛЬ не будет затронута. Если ID = 1 не существует, роль будет установлена ​​на «Benchwarmer» вместо значения по умолчанию.

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

Я понимаю, что это старый поток, но в последнее время я работал с 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 может полностью оптимизировать оператор обновления, если вызов changes () больше нуля. Так ли это на самом деле, мне неизвестно, но мужчина может мечтать, не так ли? ;)

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

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

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

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

 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 сам выбирает значение идентификатора при вставке новой строки. Но при обновлении существующей строки на основе ее name я хочу, чтобы она продолжала иметь старое значение идентификатора (очевидно!).

Я получаю настоящий 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, чтобы сказать «вставьте новый контент, если он есть, в противном случае оставьте старый контент» - например, если вы используете фиксированный запрос и связываете новые значения с заполнителями.

Расширяя Ответ Аристотеля, вы можете ВЫБРАТЬ из фиктивной таблицы «singleton» (таблица, созданная вами с одной строкой). Это позволяет избежать дублирования.

Я также сохранил переносимый пример для 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";

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

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

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

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

Следует упомянуть, что функция 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;

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;

Этот ответ был обновлен, поэтому комментарии ниже больше не применимы.

2018-05-18 СТОП-ПРЕСС.

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

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

альтернативно:

Другой совершенно другой способ сделать это: в моем приложении я устанавливаю свой rowID в памяти на long.MaxValue, когда я создаю строку в памяти. (MaxValue никогда не будет использоваться в качестве идентификатора, вы не проживете достаточно долго .... Тогда, если rowID не является этим значением, тогда он должен уже быть в базе данных, поэтому требуется ОБНОВЛЕНИЕ, если это MaxValue, тогда ему нужна вставка. Это полезно только в том случае, если вы можете отслеживать идентификаторы строк в своем приложении.

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

Я хотел бы изменить фамилию сотрудника 300 на ДЭВИС, если есть сотрудник 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 на то, какими должны быть значения.

Если кто-то хочет прочитать мое решение для 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, '$1').split(','); ///// RegEx
        var columnNames = [];
        for (i in columnParts) {
            if (typeof columnParts[i] === 'string')
                columnNames.push(columnParts[i].split(" ")[0]);
        };
        callback(columnNames);
    });
});
}

Затем программно создайте транзакции.

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

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

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

Следуя Аристотель Пагальцис и идее 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.

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

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

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL. UPSERT syntax was added to SQLite with version 3.24.0 (pending).

An UPSERT is an ordinary INSERT statement that is followed by the special ON CONFLICT clause

^{Image source: https://www.sqlite.org/images/syntax/upsert-clause.gif}

Обновления от Бернхардта:

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

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

Если не возражаете, проделайте это за две операции.

Шаги:

1) Добавьте новые элементы с помощью «ВСТАВИТЬ ИЛИ ИГНОРИРОВАТЬ»

2) Обновите существующие элементы с помощью «ОБНОВЛЕНИЕ»

Входными данными для обоих шагов является один и тот же набор новых или обновляемых элементов. Прекрасно работает с существующими элементами, которые не нуждаются в изменениях. Они будут обновлены, но с теми же данными, и, следовательно, чистый результат - без изменений.

Конечно, медленнее и т. д. Неэффективно. Ага.

Легко написать sql и поддерживать и понимать его? Определенно.

Это компромисс, который следует учитывать. Отлично подходит для небольших апсертов. Отлично подходит для тех, кто не против пожертвовать эффективностью ради удобства сопровождения кода.