SQL: A query to insert new record or replace only

问题:

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. If no row with the same id is present add new row to the table with default value for "selected"

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

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

this will not add new rows.

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 :)

回答1:

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

followed by

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

回答2:

I usually use LEFT JOIN for INSERT and DELETE and JOIN for updates. Note that it happens as one big query, not record by record base:

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