SQL：插入新记录或替换部分字段的查询

Question

我的数据库是SQLite，但我相信这个问题适用于SQL。例如，我有一个名为“students”的表格，其中包含“id”（主键）、“name”和“selected”三列。不时地，我需要从外部源更新该表格，但我只会收到一个包含id和name的表格。每当发生这样的更新时，我需要执行以下操作：

1. 如果没有具有相同id的行，则向表格中添加新行，并将“selected”设置为默认值。

2. 如果行已经存在，则仅更新“name”字段，而不改变“selected”。

这应该通过单个带占位符的查询批量完成。此外，实际情况比这更复杂，我需要编写通用代码来更新一组包含多个要更新的字段和多个“本地”字段的表格。

不幸的是，我找不到一个合适的方法来表达我对SQLite的期望。如果我使用REPLACE查询：

INSERT OR REPLACE INTO students (id, name) VALUES (:id, :name)

如果我使用UPDATE，它将清除"selected"字段：

UPDATE students SET name = :name WHERE id = :id

这不会添加新行。

那么，正确的方法是什么？我有一种感觉自己错过了非常简单的东西，并且当我得到答案时，我会感到非常愚蠢：）

- Septagram

Answer 1

INSERT or REPLACE不是通用的。每个数据库都有自己的语法（例如，SQL Server 2005及更高版本使用MERGE），而许多数据库根本没有这个功能。这种情况下的通用解决方案是使用两个语句。

UPDATE students SET name = :name WHERE id = :id

跟随着

INSERT INTO Students 
(ID, Name)
Values
(:id,:name)
WHERE 
Not exists (select * from students where ID= :id)

- Conrad Frix

Answer 2

我通常在INSERT和DELETE时使用LEFT JOIN，在更新时使用JOIN。请注意，这是作为一个大查询发生的，而不是逐条记录进行的。

UPDATE T1
FROM T1 JOIN T2 ON T1.PK = T2.PK

INSERT T1
SELECT 
FROM T2 LEFT JOIN T1 ON T2.PK = T1.PK
WHERE T1.PK IS NULL

DELETE T1
FROM T1 LEFT JOIN T2 ON T1.PK = T2.PK
WHERE T2.PK IS NULL

- Schultz9999