我发现了一些经典的“将是”解决方案“我如何插入一个新记录或更新一个如果它已经存在”，但我不能让他们中的任何一个在SQLite中工作。
我有一个定义如下的表:
CREATE TABLE Book
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Name VARCHAR(60) UNIQUE,
TypeID INTEGER,
Level INTEGER,
Seen INTEGER
我要做的是添加一个具有唯一名称的记录。如果Name已经存在，我想修改字段。
有人能告诉我怎么做吗?
当前回答
如果你没有主键，你可以插入如果不存在，然后进行更新。在使用此方法之前，表必须至少包含一个条目。
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';
其他回答
如果你没有主键，你可以插入如果不存在，然后进行更新。在使用此方法之前，表必须至少包含一个条目。
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就是你想要的。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…如果必须替换记录，将在插入新记录之前删除该记录，如果有外键级联或其他删除触发器，这可能会很糟糕。
我相信您需要UPSERT。
“INSERT OR REPLACE”在答案中没有额外的技巧，将重置任何您没有指定为NULL或其他默认值的字段。(INSERT OR REPLACE的这种行为不同于UPDATE;它和INSERT很像，因为它实际上是INSERT;然而，如果你想要的是UPDATE-if-exists，你可能想要的是UPDATE语义，并会对实际结果感到意外。)
建议的UPSERT实现的技巧基本上是使用INSERT OR REPLACE，但指定所有字段，使用嵌入式SELECT子句检索不想更改的字段的当前值。
你需要在表上设置一个约束来触发一个“冲突”，然后通过替换来解决:
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将其他字段替换为默认值。
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
使用UPSERT(语法已添加到3.24.0版本的SQLite (2018-06-04))
INSERT INTO Book (ID, Name)
VALUES (1001, 'SQLite')
ON CONFLICT (ID) DO
UPDATE SET Name=excluded.Name;
排除在外。前缀等于VALUES ('SQLite')中的值。
