插入如果不存在，否则更新?

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

我有一个定义如下的表:

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

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

有人能告诉我怎么做吗?

517120 次浏览
小开

首先更新。如果受影响的行数 = 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值。

小开

我相信你想要插入。

“INSERT OR REPLACE”在答案中没有额外的技巧，将重置任何您没有指定为NULL或其他默认值的字段。(INSERT OR REPLACE的这种行为不同于UPDATE;它和INSERT很像，因为它实际上是INSERT;然而，如果你想要的是UPDATE-if-exists，你可能想要的是UPDATE语义，并会对实际结果感到意外。)

建议的UPSERT实现的技巧基本上是使用INSERT OR REPLACE，但指定所有字段，使用嵌入式SELECT子句检索不想更改的字段的当前值。

小开
你应该使用INSERT OR IGNORE命令后跟UPDATE命令: 在下面的例子中，name是一个主键 
INSERT OR IGNORE INTO my_table (name, age) VALUES ('Karen', 34)
UPDATE my_table SET age = 34 WHERE name='Karen'

第一个命令将插入记录。如果该记录存在，它将忽略与现有主键冲突引起的错误。

第二个命令将更新记录(现在确实存在)

小开

INSERT OR REPLACE将替换其他字段为默认值。

sqlite> CREATE TABLE Book (
ID     INTEGER PRIMARY KEY AUTOINCREMENT,
Name   TEXT,
TypeID INTEGER,
Level  INTEGER,
Seen   INTEGER
);


sqlite> INSERT INTO Book VALUES (1001, 'C++', 10, 10, 0);

sqlite> SELECT * FROM Book;
1001|C++|10|10|0


sqlite> INSERT OR REPLACE INTO Book(ID, Name) VALUES(1001, 'SQLite');


sqlite> SELECT * FROM Book;
1001|SQLite|||

如果你想保留另一个磁场

  • 方法1
sqlite> SELECT * FROM Book;
1001|C++|10|10|0


sqlite> INSERT OR IGNORE INTO Book(ID) VALUES(1001);
sqlite> UPDATE Book SET Name='SQLite' WHERE ID=1001;


sqlite> SELECT * FROM Book;
1001|SQLite|10|10|0
  • 方法2

使用插入(语法已在3.24.0版本添加到SQLite (2018-06-04))

INSERT INTO Book (ID, Name)
VALUES (1001, 'SQLite')
ON CONFLICT (ID) DO
UPDATE SET Name=excluded.Name;

前缀excluded.等于VALUES ('SQLite')中的值。

小开

如果你没有主键，你可以插入如果不存在，然后进行更新。在使用此方法之前，表必须至少包含一个条目。

INSERT INTO Test
(id, name)
SELECT
101 as id,
'Bob' as name
FROM Test
WHERE NOT EXISTS(SELECT * FROM Test WHERE id = 101 and name = 'Bob') LIMIT 1;


Update Test SET id='101' WHERE name='Bob';
小开

我认为值得指出的是，如果你没有完全理解主键和独特的是如何相互作用的，这里可能会有一些意想不到的行为。

例如，如果你只想在的名字字段当前没有被占用时插入一条记录，如果是，你想要触发一个约束异常来告诉你，那么插入或替换将不会抛出异常，而是通过替换冲突的记录(使用相同的的名字的现有记录)来解决独特的约束本身。的加斯帕德在上面的他的回答中很好地演示了这一点。

如果你想要触发一个约束异常，你必须使用插入语句，并依赖于一个单独的更新命令来更新记录，一旦你知道名称没有被占用。

小开

插入是你想要的。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 ... ON CONFLICT(...) DO UPDATE SET...

如果你正在执行INSERT INTO SELECT ...，你的选择至少需要WHERE true来解决解析器关于标记ON的连接语法的歧义。

需要注意的是，INSERT OR REPLACE...会在插入一条新记录之前删除它，如果你有外键级联或其他删除触发器，这可能会很糟糕。


提交答案