How can I update the values of certain columns, ignoring the others, while also inserting a row if n-CodePudding

I have a table with a composite primary key on the columns FirstName, LastName. I want to set the Age column while leaving Address alone. If a row doesn't exist for the person, create it (and leave Address as NULL).

I tried REPLACE INTO like this:

REPLACE INTO
    answers
    (
        FirstName,
        LastName,
        Age
    )
VALUES
    (
        :first_name,
        :last_name,
        :age
    )

But this also has the effect of nulling out Address.

I found something to do with ON DUPLICATE, but that seems to be mysql only, then I heard ON CONFLICT can do something similar so I started reading this page and got very confused. I also can't find any examples on using ON CONFLICT with a composite primary key.

I'm using SQLite 3.31.1.

CodePudding user response：

You can do it with UPSERT:

INSERT INTO answers (FirstName, LastName, Age, Address)
VALUES (:first_name, :last_name, :age, :address)
ON CONFLICT DO UPDATE
SET Age = EXCLUDED.Age;

Or, if you don't want to set a value for Address in the inserted row:

INSERT INTO answers (FirstName, LastName, Age)
VALUES (:first_name, :last_name, :age)
ON CONFLICT DO UPDATE
SET Age = EXCLUDED.Age;

If the combination of :first_name and :last_name already exists in the table then the UPDATE statement will be executed which will not affect the column Address.

See the demo.