如果不存在则进行更新吗?

问题描述

我已经找到了一些经典的如何插入新记录或如果已经存在就更新一条记录"的可能"解决方案，但是我无法在SQLite中使用它们.

I've found a few "would be" solutions for the classic "How do I insert a new record or update one if it already exists" but I cannot get any of them to work in SQLite.

我有一个定义如下的表:

I have a table defined as follows:

CREATE TABLE Book
ID     INTEGER PRIMARY KEY AUTOINCREMENT,
Name   VARCHAR(60) UNIQUE,
TypeID INTEGER,
Level  INTEGER,
Seen   INTEGER

我想做的是添加一个具有唯一名称的记录.如果名称已经存在，我想修改这些字段.

What I want to do is add a record with a unique Name. If the Name already exists, I want to modify the fields.

有人可以告诉我该怎么做吗?

Can somebody tell me how to do this please?

推荐答案

看看 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.

Note that any field not in the insert list will be set to NULL if the row already exists in the table. This is why there's a subselect for the ID column: In the replacement case the statement would set it to NULL and then a fresh ID would be allocated.

如果替换行中的行要保留特定的字段值，而插入行中的字段设置为NULL，则也可以使用这种方法.

This approach can also be used if you want to leave particular field values alone if the row in the replacement case but set the field to NULL in the insert case.

例如，假设您想单独离开Seen:

For example, assuming you want to leave Seen alone:

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

这篇关于如果不存在则进行更新吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！