SQLite UPSERT / UPDATE或INSERT [英] SQLite UPSERT / UPDATE OR INSERT

问题描述

我需要对SQLite数据库执行UPSERT / INSERT OR UPDATE。

有许多情况下可以使用的命令INSERT OR REPLACE。但是如果你想保持你的id与autoincrement因为外键的位置，它不工作，因为它删除行，创建一个新的，因此这个新行有一个新的ID。

这将是表：

玩家 - （id上的主键，user_name唯一）

  | id | user_name | age | 
 ------------------------------ 
 | 1982 | johnny | 23 | 
 | 1983 | steven | 29 | 
 | 1984 | pepee | 40 | 
  

解决方案

需要强制'忽略'，这将只有当有一个关键违规时才工作。这种方式根据您在更新中指定的任何条件工作。

尝试此操作...

   - 尝试更新任何现有的行
 UPDATE players 
 SET user_name ='steven'，age = 32 
 WHERE user_name =史蒂文
 
  - 如果没有发生更新（即该行不存在），则插入一个
 INSERT INTO players（user_name，age）
 SELECT'steven'，32 
 WHERE（Select Changes（）= 0）; 
  

工作原理

'这里是使用 Where（Select Changes（）= 0）子句来确定插入是否有任何行，因为它是基于你自己的其中子句，它可以为您定义的任何内容，而不仅仅是关键违规。

在上面的示例中，没有更新（即记录不存在），则 Changes（） = 0，因此 Where 插入语句返回true，并插入指定数据的新行。

如果更新 更新现有行，然后 Changes（） = 1，在插入现在将为false，因此不会插入。

不需要强制。 / p>

I need to perform UPSERT / INSERT OR UPDATE against a SQLite Database.

There is the command INSERT OR REPLACE which in many cases can be useful. But if you want to keep your id's with autoincrement in place because of foreign keys, it does not work since it deletes the row, creates a new one and consequently this new row has a new ID.

This would be the table:

players - (primary key on id, user_name unique)

|  id   | user_name |  age   |
------------------------------
|  1982 |   johnny  |  23    |
|  1983 |   steven  |  29    |
|  1984 |   pepee   |  40    |

解决方案

I'll do you one better that doesn't require the brute-force 'ignore' which would only work if there was a key violation. This way works based on any conditions you specify in the update.

Try this...

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

How It Works

The 'magic' here is you use the Where (Select Changes() = 0) clause to determine if there are any rows for the insert, and since that is based on your own Where clause, it can be for anything you define, not just key violations.

In the above example, if there are no changes from the update (i.e. the record doesn't exist) then Changes() = 0 so the Where clause in the Insert statement returns true and a new row is inserted with the specified data.

If the Update did update an existing row, then Changes() = 1, so the 'Where' clause in the Insert will now be false and thus no insert will take place.

No brute-force needed.

这篇关于SQLite UPSERT / UPDATE或INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！