SQLite - UPSERT * не * ВСТАВИТЬ или ЗАМЕНИТЬ

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

Вставить обновление хранимой процедуры на SQL Server

Есть ли какой-нибудь умный способ сделать это в SQLite, о котором я не думал?

По сути, я хочу обновить три из четырех столбцов, если запись существует. Если ее нет, я хочу вставить запись со значением по умолчанию (NUL) для четвертого столбца.

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

(Я пытаюсь избежать издержек SELECT, чтобы определить, нужно ли мне ОБНОВИТЬ или ВСТАВИТЬ, очевидно)

Предложения?


Я не могу подтвердить, что Синтаксис на сайте SQLite для TABLE CREATE. Я не построил демо-версию, чтобы проверить его, но он не поддерживается ...

Если бы это было так, у меня есть три столбца, так что это будет выглядеть так:

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

но первые два больших двоичных объекта не вызовут конфликт, только идентификатор будет таким образом, поэтому я не могу заменить Blob1 и Blob2 (по желанию)


ОБНОВЛЕНИЯ в SQLite, когда привязка данных является полной транзакцией, то есть каждая отправляемая строка, подлежащая обновлению, требует: операторов Prepare / Bind / Step / Finalize в отличие от INSERT, который позволяет использовать функцию сброса

Жизнь объекта оператора выглядит примерно так:

  1. Создайте объект с помощью sqlite3_prepare_v2 ()
  2. Привязать значения к параметрам хоста, используя интерфейсы sqlite3_bind_.
  3. Запустите SQL, вызвав sqlite3_step ()
  4. Сбросьте инструкцию с помощью sqlite3_reset (), затем вернитесь к шагу 2 и повторите.
  5. Уничтожьте объект оператора с помощью sqlite3_finalize ().

ОБНОВЛЕНИЕ Я предполагаю, что это медленно по сравнению с INSERT, но как это сравнить с SELECT с использованием первичного ключа?

Возможно, мне следует использовать select, чтобы прочитать 4-й столбец (Blob3), а затем использовать REPLACE, чтобы написать новую запись, смешивая исходный 4-й столбец с новыми данными для первых 3 столбцов?

7.01.2009 01:47:23
SQLite - UPSERT доступен в предварительном выпуске: sqlite.1065341.n5.nabble.com/…
gaurav 11.05.2018 11:16:53
UPSERT доступен в версии 3.24.0 SQLite
pablo_worker 3.08.2018 11:12:06
18 ОТВЕТОВ
РЕШЕНИЕ

Предполагая три столбца в таблице: ID, NAME, ROLE


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

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

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

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

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

UPSERT - это специальное синтаксическое дополнение к INSERT, которое заставляет INSERT вести себя как UPDATE или no-op, если 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')
          );
850
18.12.2019 10:44:30
+1 гениально! Встроенное предложение select дает вам возможность переопределить стандартную функцию ON CONFLICT REPLACE, если вам нужно объединить / сравнить старое значение и новое значение для любого поля.
G__ 1.04.2011 16:56:10
Если на Сотрудника ссылаются другие строки с каскадным удалением, то другие строки все равно будут удалены путем замены.
Don Reba 3.08.2011 09:29:38
Это больно. SQlite нужен UPSERT.
andig 10.07.2012 12:39:13
Не могли бы вы объяснить, почему это вставит или заменит все столбцы с новыми значениями для ID = 1: в первом примере это считается ПЛОХОЙ ? Команда, которую вы там представляете, предназначена для создания новой записи с идентификатором 1, именем Джона Фу и генеральным директором роли или перезаписывает запись с идентификатором 1, если она там уже есть, этими данными (предполагается, что идентификатор является первичным ключом) , Итак, почему это плохо, если именно это происходит?
O. R. Mapper 4.01.2014 22:03:06
@Cornelius: Это ясно, но это не то, что происходит в первом примере. Первый пример предназначен для принудительной установки всех столбцов, что и происходит, независимо от того, вставлена ​​или заменена запись. Итак, почему это считается плохим? Связанный ответ также указывает только на то, что может произойти что-то плохое при указании подмножества столбцов, как во втором примере; похоже, он не раскрывает никаких негативных последствий того, что происходит в вашем первом примере, INSERT OR REPLACEа указывает значения для всех столбцов.
O. R. Mapper 26.01.2014 10:07:46

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

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

--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
                );
4
7.01.2009 02:01:10
Способ сложный.
frast 18.05.2010 07:17:36
Я думаю, что это не очень хорошая идея, потому что вам нужно сделать дважды запрос к базе данных двигателя.
Ricardo da Rocha Vitor 15.11.2018 12:57:12

Я думаю, что это может быть то, что вы ищете: ON CONFLICT .

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

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

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

Hth ...

1
7.01.2009 02:20:46
Я не думаю, что это работает, это уничтожит столбцы, отсутствующие в операторе вставки
Sam Saffron 7.01.2009 02:25:12
@Mosor: -1 от меня, извините. Это то же самое, что и REPLACEвыписка.
Alix Axel 27.04.2010 00:04:00
-1, потому что это делает удаление, а затем вставку, если первичный ключ уже существует.
frast 18.05.2010 07:13:25

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

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

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

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

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

83
7.01.2009 02:29:13
Спасибо, только что спросил это @ stackoverflow.com/questions/2717590/… . +1 от меня! =)
Alix Axel 27.04.2010 00:05:53
Если вы собираетесь проверить количество строк с помощью sqlite3_changes () на третьем шаге, убедитесь, что вы не используете дескриптор БД из нескольких потоков для изменений.
Linulin 2.07.2010 17:31:21
Не будет ли следующее менее многословным, но с тем же эффектом: 1) выберите таблицу формы идентификатора, где id = 'x' 2) if (ResultSet.rows.length == 0) обновите таблицу, где id = 'x';
Florin 12.07.2010 01:23:06
Мне бы очень хотелось, чтобы INSERT OR UPDATE были частью языка
Florin 12.07.2010 01:24:04
В конечном итоге это оказалось для меня лучшим вариантом: попытка выполнить REPLACE INTO или всегда, а вставка / обновление убивала мою производительность, когда я в основном делал обновления вместо вставок.
PherricOxide 22.04.2013 18:07:24

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

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

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

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

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

-2
26.12.2011 18:11:52
-1 INSERT OR REPLACEне является UPSERT. Смотри "ответ" Грегшлома по причине почему. Решение Эрика Б. на самом деле работает и нуждается в некоторых ответах.
Day 13.07.2011 22:49:29

ВСТАВКА ИЛИ ЗАМЕНА НЕ эквивалентны "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 будет состоять в том, чтобы изменить роль и сохранить имя.

129
16.01.2012 18:16:07
-1 от меня боюсь. Да, принятый ответ неверен, но, хотя ваш ответ указывает на проблему, он также не является ответом. Фактический ответ см. В умном решении Эрика Б. с использованием встроенного coalesce((select ..), 'new value')предложения. Я думаю, что для ответа Эрика здесь нужно больше голосов.
Day 13.07.2011 22:45:47
Верно. Эрик определенно лучший ответ и заслуживает большего количества голосов. При этом, я думаю, что, указав на проблему, я внес небольшой вклад в нахождение хорошего ответа (ответ Эрика пришел позже и основывается на примерах таблиц sql в моем ответе). Так что не уверен, если я заслуживаю -1, но не имеет значения :)
gregschlom 14.07.2011 20:31:55
+1, чтобы сместить -1 выше. лол. Интересные сроки в этой теме, хотя. Очевидно, ваш ответ был за неделю до ответа Эрика, но вы оба ответили на вопрос почти через два года после того, как он был задан. +1 для Эрика тоже, хотя для уточнения.
Rich 2.09.2011 14:56:48
@QED no, потому что delete + insert (который является заменой) - это 2 оператора dml, например, со своими собственными триггерами. Это не то же самое, что только 1 оператор обновления.
Sebas 9.02.2016 01:43:49

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

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

select case changes() WHEN 0 THEN last_insert_rowid() else <primaryKeyValue> end;
60
8.09.2011 19:12:04
+ 1. Именно то, что я пытался заменить, делая преобразование из некоторого кода SQL Server TSQL. Спасибо. SQL, который я пытался заменить, был похожUpdate <statement> If @@ROWCOUNT=0 INSERT INTO <statement>
DarrenMB 30.11.2014 00:58:34

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

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

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

107
23.05.2017 11:47:29
+1, отлично работает, но добавьте WHERE name = "about"ограничение на SELECT ... AS oldускорение. Если у вас 1м + рядов, это очень медленно.
user918938 14.04.2013 04:43:31
Хороший вопрос, +1 на ваш комментарий. Я оставлю это в стороне от ответа, потому что добавление такого WHEREпредложения требует именно такой избыточности в запросе, которую я пытался избежать в первую очередь, когда придумал этот подход. Как всегда: когда вам нужна производительность, денормализуйте - структуру запроса, в данном случае.
Aristotle Pagaltzis 27.04.2013 22:02:58
Вы можете упростить пример Аристотеля до этого, если хотите: INSERT OR REPLACE INTO page (id, name, title, content, author) SELECT id, 'about', 'About this site', content, 42 FROM ( SELECT NULL ) LEFT JOIN ( SELECT * FROM page WHERE name = 'about' )
jcox 18.08.2016 15:07:36
Не излишне ли это ON DELETEзапускает триггеры, когда он выполняет замену (то есть обновление)?
Karakuri 20.01.2017 18:00:30
Это, безусловно, вызовет ON DELETEтриггеры. Незнаю о излишне. Для большинства пользователей это, вероятно, будет ненужным, даже нежелательным, но, возможно, не для всех пользователей. Аналогичным образом, из-за того, что он также будет каскадно удалять любые строки с внешними ключами в рассматриваемой строке - вероятно, это проблема для многих пользователей. К сожалению, SQLite не имеет ничего общего с настоящим UPSERT. ( INSTEAD OF UPDATEЯ полагаю, за исключением подделки его с помощью триггера.)
Aristotle Pagaltzis 19.02.2017 06:02:06

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

Я также сохранил пример переносимого через 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";
5
23.05.2017 12:10:47

Вот решение, которое на самом деле является UPSERT (ОБНОВЛЕНИЕ или ВСТАВКА) вместо ВСТАВКИ ИЛИ ЗАМЕНЫ (которая работает по-разному во многих ситуациях).

Это работает так:
1. Попробуйте обновить, если существует запись с таким же Id.
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;
13
27.02.2014 22:47:54
Мне кажется, это лучшее решение, чем у Эрика. Однако INSERT INTO Contact (Id, Name) SELECT 3, 'Bob' WHERE changes() = 0;должно также работать.
bkausbk 2.02.2015 12:12:18
Спасибо, парень, он также работает в WebSQL (используя плагин Cordova и SQLite)
Zappescu 31.01.2017 15:45:16
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;
-4
2.05.2014 03:49:17
Это слишком сложно, SQL может прекрасно справиться с этим в одном запросе
Robin Kanters 18.01.2016 14:31:40

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

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

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

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

введите описание изображения здесь

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

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

82
23.04.2019 08:55:08
Аминь. Простое лучше, чем сложное. Это немного проще, чем принятый ответ.
kkurian 20.07.2016 15:30:56
Я думал, что вы не можете сделать INSERT INTO ... ГДЕ в sqlite? Это синтаксическая ошибка для меня в sqlite3
Charlie Martin 24.05.2017 05:11:53
@CharlieMartin правильно. Этот синтаксис недопустим для SQLite - это то, что запросил OP. Предложение WHEREне может быть добавлено к INSERTутверждению: sqlite-insert ...
colminator 24.10.2017 16:24:46
Этот ответ заставил меня потерять много времени, вопрос касается SQLITE, и я не знал, что INSERT WHERE не был поддержан в sqite. Пожалуйста, добавьте это примечание, что он недействителен для sqlite в вашем ответе
Miquel 30.12.2017 18:28:28
@colminator и другие: я исправил его оператор SQL, используя ваше предложение.
F Lekschas 24.07.2018 23:16:58

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

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

1
29.01.2016 06:20:46

Если кто-то хочет прочитать мое решение для 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, пожалуйста, обратитесь к официальной ссылке

3
31.01.2017 15:57:00

Вслед за Аристотель 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

2
3.01.2018 17:05:48
COALESCE(old.id, new.id)определенно не так с автоинкрементным ключом. И хотя «оставить большую часть строки без изменений, за исключением случаев, когда значения отсутствуют» звучит как сценарий использования, который кто-то может иметь на самом деле, я не думаю, что это то, что люди ищут, когда ищут, как сделать UPSERT.
Aristotle Pagaltzis 31.12.2017 22:28:16
@AristotlePagaltzis прошу прощения, если я ошибаюсь, я не использую автоинкременты. Что я ищу с этим запросом, чтобы обновить только несколько столбцов или вставить полную строку с предоставленными значениями, если он не существует . Я играл с вашим запросом, и я не смог достичь его при вставке: столбцы, выбранные из oldтаблицы, в которой они назначены NULL, а не значения, указанные в new. Это причина для использования COALESCE. Я не эксперт в sqlite, я тестировал этот запрос и, кажется, работает для этого случая, я был бы очень признателен, если бы вы могли указать мне на решение с автоинкрементами
Miquel 2.01.2018 13:12:21
В случае автоинкрементного ключа вы хотите вставить его NULLв качестве ключа, потому что это говорит SQLite вместо этого вставить следующее доступное значение.
Aristotle Pagaltzis 3.01.2018 16:38:30
Я не говорю, что вам не следует делать то, что вы делаете (если вам это нужно, тогда вам это нужно), просто это не совсем ответ на этот вопрос. Обычно UPSERT означает, что у вас есть строка, которую вы хотите сохранить в таблице, и вы просто не знаете, есть ли у вас соответствующая строка для ввода значений или вам нужно вставить их как новую строку. Ваш вариант использования таков: если у вас уже есть подходящая строка, вы хотите игнорировать большинство значений из новой строки . Это нормально, это не тот вопрос, который был задан.
Aristotle Pagaltzis 3.01.2018 17:12:46

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

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

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

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

введите описание изображения здесь

Источник изображения: https://www.sqlite.org/images/syntax/upsert-clause.gif

10
11.07.2018 01:19:14

Вы действительно можете сделать переход в 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
8
12.07.2018 13:01:25

Если вы не против сделать это в две операции.

шаги:

1) Добавьте новые предметы с помощью «INSERT OR IGNORE»

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

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

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

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

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

0
5.01.2020 23:49:32
Примечание для всех: ВСТАВИТЬ ИЛИ ЗАМЕНИТЬ НЕ то, о чем этот пост. INSERT OR REPLACE создаст новую строку в вашей таблице с новым идентификатором. Это не ОБНОВЛЕНИЕ.
sapbucket 5.01.2020 23:50:23