我找到了一些经典的“将来”的解决方案,如果它已经存在,我将如何插入新记录或更新一个记录,但我无法让它们在SQLite中工作 .
我有一个表定义如下:
CREATE TABLE Book
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Name VARCHAR(60) UNIQUE,
TypeID INTEGER,
Level INTEGER,
Seen INTEGER
我想要做的是添加一个具有唯一名称的记录 . 如果名称已存在,我想修改字段 .
有人可以告诉我该怎么做?
9 回答
看看http://sqlite.org/lang_conflict.html .
你想要的东西:
请注意,如果表中已存在该行,则不在插入列表中的任何字段将设置为NULL . 这就是
ID
列的子选择的原因:在替换的情况下,语句将其设置为NULL,然后将分配新的ID .如果要在替换案例中的行中单独保留特定字段值,但在插入案例中将字段设置为NULL,也可以使用此方法 .
例如,假设您要单独留下
Seen
:您应该使用
INSERT OR IGNORE
命令后跟UPDATE
命令:在以下示例中,name
是主键:第一个命令将插入记录 . 如果记录存在,它将忽略与现有主键冲突导致的错误 .
第二个命令将更新记录(现在肯定存在)
您需要在表上设置约束以触发“conflict”,然后通过执行替换来解析:
然后你可以发出:
“SELECT * FROM data”将为您提供:
请注意,data.id为“3”而不是“1”,因为REPLACE执行DELETE和INSERT,而不是UPDATE . 这也意味着您必须确保定义所有必需的列,否则您将获得意外的NULL值 .
首先更新它 . 如果 affected row count = 0则插入它 . 它最简单,最适合所有 RDBMS .
INSERT OR REPLACE
会将其他字段(TypeID
,Level
)替换为默认值 .我正在使用这个
你也可以使用
但我认为第一种方法更容易阅读
如果您没有主键,则可以插入(如果不存在),然后执行更新 . 在使用此表之前,该表必须至少包含一个条目 .
我相信你想UPSERT .
“INSERT OR REPLACE”在该答案中没有额外的诡计会将您未指定的任何字段重置为NULL或其他默认值 . (INSERT OR REPLACE的这种行为与UPDATE不同;它与INSERT完全相同,因为它实际上是INSERT;但是如果您想要的是UPDATE-if-exists,您可能需要UPDATE语义并且会对实际结果感到不愉快 . )
建议的UPSERT实现的技巧基本上是使用INSERT OR REPLACE,但指定所有字段,使用嵌入的SELECT子句来检索您不想更改的字段的当前值 .
Upsert就是你想要的 .
UPSERT
语法已添加到SQLite版本3.24.0(2018-06-04) .请注意,此时实际的单词“UPSERT”不是upsert语法的一部分 .
正确的语法是
INSERT INTO ... ON CONFLICT(...) DO UPDATE SET...
如果您正在做
INSERT INTO SELECT ...
,您的选择需要至少WHERE true
来解决使用连接语法的令牌ON
的解析器歧义 .请注意,如果必须替换,
INSERT OR REPLACE...
将在插入新记录之前删除记录,如果您有外键级联或其他删除触发器,这可能是错误的 .我认为's worth pointing out that there can be some unexpected behaviour here if you don'彻底了解 PRIMARY KEY 和 UNIQUE 如何相互作用 .
例如,如果您只想在当前没有使用NAME字段时插入记录,如果是,则需要触发约束异常来告诉您,那么 INSERT OR REPLACE 将不会抛出异常而是将解析 UNIQUE 通过替换冲突记录(具有相同NAME的现有记录)来约束自身 . Gaspard's在上面的his answer中表现得非常好 .
如果要触发约束异常,则必须使用 INSERT 语句,并且一旦知道未采用该名称,就依赖单独的 UPDATE 命令来更新记录 .