SQLite的UPSERT /更新或插入
我需要对SQLite数据库执行UPSERT / INSERT OR UPDATE。
INSERT OR REPLACE命令在很多情况下都是有用的。 但是，如果你想保留你的id与autoincrement适当的地方由于外键，它不起作用，因为它删除行，创建一个新的，因此这个新行有一个新的ID。
这将是表格：
播放器 - （主键ID，用户名唯一）
| id | user_name | age |
------------------------------
| 1982 | johnny | 23 |
| 1983 | steven | 29 |
| 1984 | pepee | 40 |
我会做一个更好的，不需要暴力的'忽略'，只有在发生重大违规行为时才会生效。 这种方式基于您在更新中指定的任何条件。
尝试这个...
-- Try to update any existing row
UPDATE players
SET user_name='steven', age=32
WHERE user_name='steven';
-- If no update happened (i.e. the row didn't exist) then insert one
INSERT INTO players (user_name, age)
SELECT 'steven', 32
WHERE (Select Changes() = 0);
怎么运行的
这里的'魔术'是你使用Where (Select Changes() = 0)子句来确定是否有插入行，并且由于它基于你自己的Where子句，它可以用于你定义的任何东西，而不是只是关键违规。
在上面的例子中，如果没有更新（即记录不存在），那么Changes() = 0，所以Insert语句中的Where子句返回true，并且插入一个新的行与指定的数据。
如果Update确实更新了现有的行，那么Changes() = 1，因此Insert的'Where'子句现在将为false，因此不会发生插入。
不需要蛮力。
问答风格
那么，经过数小时的研究和解决问题后，我发现有两种方法可以实现这一点，具体取决于表格的结构以及是否激活了外键限制以保持完整性。 我想以清晰的格式分享这些内容，以节省一些时间给可能属于我的人。
选项1：您可以负担删除行
换句话说，你没有外键，或者如果你有它们，你的SQLite引擎被配置为没有完整性异常。 要走的路是INSERT OR REPLACE。 如果您尝试插入/更新ID已存在的播放器，SQLite引擎将删除该行并插入您提供的数据。 现在问题出现了：如何保持旧ID关联？
假设我们想用数据user_name ='steven'和年龄= 32来UPSERT。
看看这段代码：
INSERT INTO players (id, name, age)
VALUES (
coalesce((select id from players where user_name='steven'),
(select max(id) from drawings) + 1),
32)
诀窍在于合并。 它返回用户'steven'的id（如果有的话），否则返回一个新鲜的id。
选项2：您不能删除该行
在弄清了以前的解决方案之后，我意识到在我的情况下，最终会破坏数据，因为这个ID作为其他表的外键。 此外，我使用子句ON DELETE CASCADE创建了表，这意味着它会默默删除数据。 危险的。
所以，我首先想到了一个IF子句，但SQLite只有CASE。 如果EXISTS（从user_name ='steven'的玩家中选择id），则此CASE不能用于（或者至少我没有管理它）执行一个UPDATE查询，如果没有，则INSERT。 不行。
然后，最后我用了蛮力，成功了。 逻辑上，对于您要执行的每个UPSERT，首先执行INSERT或IGNORE以确保与我们的用户有一行，然后使用您尝试插入的完全相同的数据执行UPDATE查询。
与以前相同的数据：user_name ='steven'和年龄= 32。
-- make sure it exists
INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32);
-- make sure it has the right data
UPDATE players SET user_name='steven', age=32 WHERE user_name='steven';
就这样！
编辑
正如Andy所说的那样，试图首先插入然后更新可能导致触发器的触发次数超过预期。 这在我看来并不是一个数据安全问题，但确实发射不必要的事件没有多大意义。 因此，改进的解决方案将是：
-- Try to update any existing row
UPDATE players SET user_name='steven', age=32 WHERE user_name='steven';
-- Make sure it exists
INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32);
所有问题都显示了答案，它完全没有考虑触发器（可能还有其他副作用）。 解决方案如
INSERT OR IGNORE ...
UPDATE ...
导致两行触发器在行不存在时执行（插入然后更新）。
正确的解决方案是
UPDATE OR IGNORE ...
INSERT OR IGNORE ...
在这种情况下，只有一条语句被执行（当行存在或不存在时）。
链接地址: http://www.djcxy.com/p/19793.html