我发现了一些经典的“将是”解决方案“我如何插入一个新记录或更新一个如果它已经存在”,但我不能让他们中的任何一个在SQLite中工作。

我有一个定义如下的表:

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

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

有人能告诉我怎么做吗?


当前回答

你需要在表上设置一个约束来触发一个“冲突”,然后通过替换来解决:

CREATE TABLE data   (id INTEGER PRIMARY KEY, event_id INTEGER, track_id INTEGER, value REAL);
CREATE UNIQUE INDEX data_idx ON data(event_id, track_id);

然后你可以发出:

INSERT OR REPLACE INTO data VALUES (NULL, 1, 2, 3);
INSERT OR REPLACE INTO data VALUES (NULL, 2, 2, 3);
INSERT OR REPLACE INTO data VALUES (NULL, 1, 2, 5);

SELECT * FROM data会给你:

2|2|2|3.0
3|1|2|5.0

注意数据。id是“3”而不是“1”,因为REPLACE执行的是DELETE和INSERT,而不是UPDATE。这也意味着您必须确保您定义了所有必要的列,否则您将得到意外的NULL值。

其他回答

Upsert就是你想要的。UPSERT语法在3.24.0版本(2018-06-04)添加到SQLite中。

CREATE TABLE phonebook2(
  name TEXT PRIMARY KEY,
  phonenumber TEXT,
  validDate DATE
);

INSERT INTO phonebook2(name,phonenumber,validDate)
  VALUES('Alice','704-555-1212','2018-05-08')
  ON CONFLICT(name) DO UPDATE SET
    phonenumber=excluded.phonenumber,
    validDate=excluded.validDate
  WHERE excluded.validDate>phonebook2.validDate;

请注意,在这一点上,实际的单词“UPSERT”不是UPSERT语法的一部分。

正确的语法是

插入…在冲突(…)做更新设置…

如果你正在做INSERT INTO SELECT…你的选择至少需要WHERE为真来解决解析器关于连接语法标记ON的歧义。

请注意INSERT OR REPLACE…如果必须替换记录,将在插入新记录之前删除该记录,如果有外键级联或其他删除触发器,这可能会很糟糕。

首先更新。如果受影响的行数= 0,则插入它。它最简单,适用于所有的RDBMS。

看看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。

如果您希望在替换情况下保留特定的字段值,而在插入情况下将字段设置为NULL,也可以使用这种方法。

例如,假设你想让Seen独处:

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

你需要在表上设置一个约束来触发一个“冲突”,然后通过替换来解决:

CREATE TABLE data   (id INTEGER PRIMARY KEY, event_id INTEGER, track_id INTEGER, value REAL);
CREATE UNIQUE INDEX data_idx ON data(event_id, track_id);

然后你可以发出:

INSERT OR REPLACE INTO data VALUES (NULL, 1, 2, 3);
INSERT OR REPLACE INTO data VALUES (NULL, 2, 2, 3);
INSERT OR REPLACE INTO data VALUES (NULL, 1, 2, 5);

SELECT * FROM data会给你:

2|2|2|3.0
3|1|2|5.0

注意数据。id是“3”而不是“1”,因为REPLACE执行的是DELETE和INSERT,而不是UPDATE。这也意味着您必须确保您定义了所有必要的列,否则您将得到意外的NULL值。

我认为值得指出的是,如果您没有完全理解PRIMARY KEY和UNIQUE如何交互,可能会出现一些意想不到的行为。

例如,如果您希望仅在NAME字段当前未被占用时插入一条记录,并且如果已被占用,则希望触发一个约束异常来告诉您,那么insert OR REPLACE将不会抛出异常,而是通过替换冲突记录(具有相同NAME的现有记录)来解决UNIQUE约束本身。Gaspard在上面的回答中很好地证明了这一点。

如果希望触发约束异常,则必须使用INSERT语句,并在知道名称未被占用后依赖单独的UPDATE命令更新记录。