即使表中没有此 id 更新返回 0 [英] Update return 0 even there is no this id in table

问题描述

我在 Visual Studio 中使用 sqlite.

I am using sqlite in visual studio.

我正在尝试更新我的表中不存在的记录.

I am trying to update a record which does not exist in my table.

代码如下:

const char* sqlUpdateTable = "UPDATE MyTable SET name = '25th' WHERE id = 25;";

rc = sqlite3_exec(db, sqlUpdateTable, NULL, NULL, &error);
if (rc)
{
    cerr << "Error executing SQLite3 statement: " << sqlite3_errmsg(db) << endl << endl;
    sqlite3_free(error);
}
else
{
    cout << "Updated MyTable." << endl << endl;
}

我总是会看到更新的 MyTable".然而，它没有.

I will always see "Updated MyTable". However, it didn't.

推荐答案

您的实际问题:

我需要更新一条特定的记录，例如:PRIMARY KEY id = 10，如果这条记录不存在，我需要插入它.

I need to update a specific record, ex: PRIMARY KEY id = 10, if this record doesn't exist, I need to insert it.

现代版本的 Sqlite 支持 Postgres 风格的 UPSERT 表示法:

Modern versions of Sqlite support Postgres-style UPSERT notation:

INSERT INTO myTable(id, name) VALUES (25, '25th') ON CONFLICT (id) DO UPDATE SET name = '25th';

假设 id 是主键或唯一列，这将插入新行或使用该 id 更新现有行的名称列.

Assuming id is a primary key or unique column, this will either insert a new row or update the name column of an existing row with that id.

旧版本可以使用 INSERT OR REPLACE 来获得类似的东西:

Older versions can use INSERT OR REPLACE to get something similar:

INSERT INTO myTable(id, name) VALUES (25, 'Original Name');
-- At some later point...
INSERT OR REPLACE INTO myTable(id, name) VALUES (25, '25th');

来自 REPLACE 冲突解决 文档:

当发生 UNIQUE 或 PRIMARY KEY 约束冲突时，REPLACE 算法会在插入或更新当前行之前删除导致约束冲突的预先存在的行，并且命令继续正常执行.

When a UNIQUE or PRIMARY KEY constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally.

这篇关于即使表中没有此 id 更新返回 0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！