我发现了一些经典的“将是”解决方案“我如何插入一个新记录或更新一个如果它已经存在”，但我不能让他们中的任何一个在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';
其他回答
看看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"));
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')中的值。
我认为值得指出的是，如果您没有完全理解PRIMARY KEY和UNIQUE如何交互，可能会出现一些意想不到的行为。
例如，如果您希望仅在NAME字段当前未被占用时插入一条记录，并且如果已被占用，则希望触发一个约束异常来告诉您，那么insert OR REPLACE将不会抛出异常，而是通过替换冲突记录(具有相同NAME的现有记录)来解决UNIQUE约束本身。Gaspard在上面的回答中很好地证明了这一点。
如果希望触发约束异常，则必须使用INSERT语句，并在知道名称未被占用后依赖单独的UPDATE命令更新记录。
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。
