SQL:插入新记录或仅替换某些字段的查询 [英] SQL: A query to insert new record or replace only some fields

问题描述

我的数据库是 SQLite，但我确定问题一般适用于 SQL.说，我有一个表students"，其中包含id"(主键)、name"、selected"列.有时我需要从外部源更新所述表，但我只收到一个包含 id 和名称的表.当每一行发生所述更新时，我需要:

My database is SQLite, but I'm sure question applies to SQL in general. Say, I have a table "students" with columns "id" (primary key), "name", "selected". From time to time I need to update said table from the outside source, but I only receive a table of ids and names. When said update occurs for each line I need to:

1. 如果没有具有相同 id 的行，则将新行添加到表中，默认值为selected"

1. If no row with the same id is present add new row to the table with default value for "selected"

如果行已经存在，只更新name"字段，不修改selected"

If row already exists update only "name" field, untouching "selected"

这应该通过带有占位符的单个查询批量完成.另外，情况简化了，实际上我需要编写用于更新一组表的通用代码，每个表包含几个要更新的字段和几个本地"字段.

This should be done in a batch with a single query with placeholders. Also, the case is simplified, actually I need to write universal code for updating a set of tables, each containing several fields to be updated and several "local" fields.

不幸的是，我找不到合适的方式来表达我对 SQLite 的渴望.如果我使用 REPLACE 查询:

Unfortunatelly, I cannot find a suitable way to express my desire to SQLite. If I use REPLACE query:

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

这将清除选定"字段，而如果我使用更新:

this will clear away the "selected" field, while if I use UPDATE:

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

这不会添加新行.

那么，正确的做法是什么?我有一种感觉，我错过了一些非常非常简单的东西，当我得到答案时，我会觉得非常愚蠢:)

So, what is the right way of doing it? I got a feeling I'm missing something very-very simple, and that I'm going to feel very-very stupid when I get the answer :)

推荐答案

INSERT or REPLACE 不是通用的.每个 DB 都有自己的语法(例如 SQL Server 2005 及更高版本的 MERGE)，但许多数据库并没有全部.对此的通用解决方案是两个语句.

INSERT or REPLACE isn't universal. Each DB has its own syntax for it (MERGE for SQL Server 2005 and later for instance), and many don't have it all. The universal solution for this is two statements.

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)

这篇关于SQL:插入新记录或仅替换某些字段的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！