SQL: A query to insert new record or replace only some fields

Question

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

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)