如果不存在则插入其他更新?

2022-01-09 00:00:00 insert upsert sqlite exists merge-conflict-resolution

我为经典的如何插入新记录或更新已存在的记录"找到了一些可能"的解决方案，但我无法让它们中的任何一个在 SQLite 中工作.

I've found a few "would be" solutions for the classic "How do I insert a new record or update one if it already exists" but I cannot get any of them to work in SQLite.

我有一个表定义如下:

CREATE TABLE Book ID INTEGER PRIMARY KEY AUTOINCREMENT, Name VARCHAR(60) UNIQUE, TypeID INTEGER, Level INTEGER, Seen INTEGER

我想要做的是添加一个具有唯一名称的记录.如果名称已经存在，我想修改字段.

What I want to do is add a record with a unique Name. If the Name already exists, I want to modify the fields.

谁能告诉我怎么做?

推荐答案

看看http://sqlite.org/lang_conflict.html.

你想要这样的东西:

insert or replace into Book (ID, Name, TypeID, Level, Seen) values ((select ID from Book where Name = "SearchName"), "SearchName", ...);

请注意，如果该行已存在于表中，则任何不在插入列表中的字段都将设置为 NULL.这就是 ID 列有一个子选择的原因:在替换情况下，语句会将其设置为 NULL，然后分配一个新的 ID.

Note that any field not in the insert list will be set to NULL if the row already exists in the table. This is why there's a subselect for the ID column: In the replacement case the statement would set it to NULL and then a fresh ID would be allocated.

如果您想在替换情况下的行中单独保留特定字段值，但在插入情况下将该字段设置为 NULL，则也可以使用此方法.

This approach can also be used if you want to leave particular field values alone if the row in the replacement case but set the field to NULL in the insert case.

例如，假设您想单独留下 Seen:

For example, assuming you want to leave Seen alone:

insert or replace into Book (ID, Name, TypeID, Level, Seen) values ( (select ID from Book where Name = "SearchName"), "SearchName", 5, 6, (select Seen from Book where Name = "SearchName"));

相关文章