SQLite - UPSERT不插入或替换

http://en.wikipedia.org/wiki/Upsert

在SQL Server上插入更新存储过程

在SQLite中是否有一些我没有想到的聪明的方法来做到这一点?

基本上，如果记录存在，我想要更新四列中的三列，如果它不存在，我想插入记录的默认值(NUL)为第四列。

ID是一个主键，所以UPSERT只会有一条记录。

(我试图避免选择的开销，以确定我是否需要更新或插入明显)

建议吗?

我无法确认在SQLite网站上创建表的语法。我还没有构建一个演示来测试它，但它似乎不受支持。

如果是的话，我有三列，所以它实际上是这样的:

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    Blob1 BLOB ON CONFLICT REPLACE, 
    Blob2 BLOB ON CONFLICT REPLACE, 
    Blob3 BLOB 
);

但前两个斑点不会引起冲突，只有ID会所以我假设Blob1和Blob2不会被替换(如期望的那样)

SQLite中绑定数据时的更新是一个完整的事务，也就是说每个要更新的发送行都需要:Prepare/Bind/Step/Finalize语句与允许使用重置函数的INSERT不同

语句对象的生命周期是这样的:

使用sqlite3_prepare_v2()创建对象使用sqlite3_bind_接口将值绑定到主机参数。通过调用sqlite3_step()运行SQL 使用sqlite3_reset()重置语句，然后返回步骤2并重复。使用sqlite3_finalize()销毁语句对象。

更新我猜是慢与插入相比，但它如何比较选择使用主键?

也许我应该使用选择读取第4列(Blob3)，然后使用REPLACE写一个新记录混合原始的第4列与前3列的新数据?

当前回答

SELECT COUNT(*) FROM table1 WHERE id = 1;

如果COUNT(*) = 0

INSERT INTO table1(col1, col2, cole) VALUES(var1,var2,var3);

else if COUNT(*) > 0

UPDATE table1 SET col1 = var4, col2 = var5, col3 = var6 WHERE id = 1;

2014-05-02 03:49:17

其他回答

插入或替换不等同于“UPSERT”。

假设我有一个包含id、name和role字段的Employee表:

INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO")
INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey")

砰，你把1号员工的名字弄丢了。SQLite已将其替换为默认值。

UPSERT的预期输出是更改角色并保留名称。

2010-11-23 07:46:00

刚刚读了这篇文章，很失望地发现，要找到这个“UPSERT”并不容易，我进一步调查了一下……

实际上，您可以在SQLITE中直接轻松地完成此操作。

而不是使用:INSERT INTO

用途:插入或替换

这正是你想要它做的!

2010-10-07 11:00:32

我意识到这是一个旧线程，但我最近一直在sqlite3中工作，并提出了这个方法，它更适合我动态生成参数化查询的需求:

insert or ignore into <table>(<primaryKey>, <column1>, <column2>, ...) values(<primaryKeyValue>, <value1>, <value2>, ...); 
update <table> set <column1>=<value1>, <column2>=<value2>, ... where changes()=0 and <primaryKey>=<primaryKeyValue>;

它仍然是2个查询，在更新上有一个where子句，但似乎做到了这一点。我还在脑海中设想，如果对changes()的调用大于零，sqlite可以完全优化掉更新语句。我不知道它是否真的做到了，但一个人可以梦想，不是吗?；）

为了获得额外的分数，您可以追加这一行，它将返回行id，无论它是新插入的行还是现有的行。

select case changes() WHEN 0 THEN last_insert_rowid() else <primaryKeyValue> end;

2011-09-08 19:12:04

如果你通常做更新，我会..

开始事务进行更新检查行数如果是0，则插入提交

如果你通常做插入，我会

开始事务尝试插入检查主键违反错误如果出现错误，就进行更新提交

这样你就避免了选择，你在Sqlite上的事务是健全的。

2009-01-07 02:29:13

这种方法混合了回答这个问题的一些其他方法，并结合了CTE(公共表表达式)的使用。我将介绍这个查询，然后解释我为什么这么做。

如果有员工300的话，我想把员工300的姓改成DAVIS。否则，我将增加一个新员工。

表名:员工列:id、first_name、last_name

查询为:

INSERT OR REPLACE INTO employees (employee_id, first_name, last_name)
WITH registered_employees AS ( --CTE for checking if the row exists or not
    SELECT --this is needed to ensure that the null row comes second
        *
    FROM (
        SELECT --an existing row
            *
        FROM
            employees
        WHERE
            employee_id = '300'

        UNION

        SELECT --a dummy row if the original cannot be found
            NULL AS employee_id,
            NULL AS first_name,
            NULL AS last_name
    )
    ORDER BY
        employee_id IS NULL --we want nulls to be last
    LIMIT 1 --we only want one row from this statement
)
SELECT --this is where you provide defaults for what you would like to insert
    registered_employees.employee_id, --if this is null the SQLite default will be used
    COALESCE(registered_employees.first_name, 'SALLY'),
    'DAVIS'
FROM
    registered_employees
;

基本上，我使用CTE来减少必须使用select语句来确定默认值的次数。因为这是一个CTE，所以我们只需要从表中选择我们想要的列，INSERT语句就使用了这一点。

现在可以通过将COALESCE函数中的空值替换为值来决定要使用的缺省值。

2016-01-29 06:20:46

SQLite - UPSERT不插入或替换

推荐文章

最新文章

标签