SQL: query to insert a new record or replace only certain fields

Question

My database is SQLite, but I'm sure the question is about SQL in general. Let's say I have a table called “students” with columns “id” (primary key), “name”, “selected”. From time to time I need to update the table from an external source, but I only get a table of identifiers and names. When an update occurs for each row, I need:

• If no rows with the same identifier are added, add a new row to the table with the default value for "selected"

• If the row already exists, update only the "name" field by disabling "selected"

This should be done in a single request package with placeholders. In addition, the matter is simplified; in fact, I need to write universal code to update a set of tables, each of which contains several fields for updating and several "local" fields.

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

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

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

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

this will not add new lines.

So what is the right way to do this? I got the feeling that I was missing something very very simple, and that I would feel very very stupid when I get an answer :)

Answer 1

INSERT or REPLACE . (MERGE SQL Server 2005 , ), . .

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)

Answer 2

LEFT JOIN INSERT DELETE 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